The easy way to do this would be to use an SQL SPLIT function in your statement. This function was added with SQL Server 2016, previous versions would require a
User Defined Function to have been installed. If this is the case there are plenty available on sites such as Sql Server Central
Good Read:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[
^]
This statement is using the 2016+ built in function:
DECLARE @ID NVARCHAR(3) = '001'
DECLARE @LocationIDs NVARCHAR(100) = '1,2,3'
SELECT Name
FROM Table
WHERE Id = @ID
AND LocationID IN (SELECT value FROM STRING_SPLIT(@LocationIDs, ','))
The second option is a little more complicated, requiring SQL User Defined Types as well as different syntax within the ADO call. The advantage is you can pass in more complex types such as DataTables, DataReaders, or iEnumerables
SQL Portion
CREATE TYPE dbo.UserTableType_INTs AS TABLE( IntValue INT NOT NULL)
GO
CREATE PROCEDURE DBO.usp_MyProcedure (
@ID NVARCHAR(100),
@LocationIDs dbo.UserTableType_INTs READONLY
) AS
BEGIN
SELECT Name
FROM Table
WHERE Id = @ID
AND LocationID IN @LocationIDs
END
GO
And calling it via ADO in C#
DataTable LocationIDs = new DataTable();
LocationIDs.Clear();
LocationIDs.Columns.Add("IntValue");
LocationIDs.Rows.Add(1);
LocationIDs.Rows.Add(2);
LocationIDs.Rows.Add(3);
SqlCommand cmd = new SqlCommand(sqlInsert, connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", "001");
SqlParameter tvpParam = cmd.Parameters.AddWithValue("@LocationIDs", LocationIDs);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.UserTableType_INTs";