How to pass string array in SQL parameter to IN clause in SQL

Question

A logic which I am doing in a complex way.

I just need to execute this query in a stored procedure:

select Sizes, SUM(Quantity)
from tbl_SizeBreakup
where (Brand=@brand)
  and (Combo in ('1','2')) ...

The combo I must pass in SQL parameter using in C# is

DataSet dt = new DataSet();
cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "sp_Accessories";
cmd.Connection = con;

cmd.Parameters.AddRange(
    new SqlParameter[] {
        new SqlParameter("@Mode",mode),
        new SqlParameter("@Combo",combo),
    }}

So if I pass 1 parameter, working as expected. The combo which I should pass is a string[] (string array). The array length can be anything depends on the user selecting in UI.

My question is, how to pass string[] to new SqlParameter("@Combo",combo)?

My stored procedure..

ALTER proc [dbo].[sp_Accessories]
(  
@Mode varchar(50)=null,
@id int=null,
@brand varchar(50)=null,
@department varchar(MAX)=null,
@season varchar(50)=null,
@groupname varchar(MAX)=null,
@styles varchar(50)=null,
@combo varchar(50)=null,
@combo_color nvarchar(max)=null,
)
as
if @Mode='getsizewise'
begin
select Sizes,SUM(Quantity) from tbl_SizeBreakup where (Brand=@brand) and (Department=@department) and (Season=@season) and (Group_Name=@groupname) and (Style=@styles) 
and (Combo_Color=@color) and (Total_Add_Qty='Total Quantity') Group By Sizes
end

Answer 1

Introduction: Even though the OP already accepted an answer, I thought it would be better to share my experience, because I belive the approach I'm about to show is better then the one accepted.

I find that the best way to pass Arrays to sql server database is using a user defined table type and c# DataTable. In your case, since you want to pass a string array of one dimension, it's fairly easy:

First you need to create a user defined table type in your database:

 CREATE TYPE dbo.StringArray As Table (
    StringItem varchar(50) -- you can use any length suited for your needs
)

Then you need to create a datatable in your c# code:

DataTable dt = new DataTable();
dt.Columns.Add("StringItem", typeof(System.String));

Then change your stored procedure to accept this data type as a parameter:

ALTER proc [dbo].[sp_Accessories]
(  
@Mode varchar(50)=null,
@id int=null,
@brand varchar(50)=null,
@department varchar(MAX)=null,
@season varchar(50)=null,
@groupname varchar(MAX)=null,
@styles varchar(50)=null,
@combo dbo.StringArray Readonly=null, -- NOTE THIS CHANGE
@combo_color nvarchar(max)=null,
)
as
if @Mode='getsizewise'
begin
select Sizes,SUM(Quantity) from tbl_SizeBreakup where (Brand=@brand) and
(Department=@department) and (Season=@season) and (Group_Name=@groupname) and (Style=@styles) 
and (Combo_Color=@color) and (Total_Add_Qty='Total Quantity') 
and comboColumn in(select StringItem from @Combo) -- NOTE THIS CHANGE
Group By Sizes
end

Then you need to convert the string array to a dataTable in your c# code.

foreach (string s in YourStringArray) {
    string[] temp = {s};
    dt.Rows.Add(temp);
}

Add the DataTable as a parameter to the stored procedure:

System.Data.SqlClient.SqlParameter sp = new Data.SqlClient.SqlParameter();
sp.SqlDbType = SqlDbType.Structured;
sp.Value = dt;
sp.ParameterName = "@Combo";
cmd.Parameters.Add(sp);

Build and run.

This approach should have better performance then using an sql user defined function, and also can be used for different data types. this is one of the best reasons to use it: Consider a scenario where you need to pass an array of Dates: the csv approach requires sql to convert each string to a date, while with this approach you can simply pass the dates as is, without converting them to strings and then back to dates.Also, you can pass 2 dimensions array or dictionaries, all you have to do is create the appropriate user defined data type in your sql database.

Note: code written directly here, there might be some typos.

Answer 2

You can pass a list in a csv(coma separated values): '1,2,3', this string in the SP is useless, but you can convert to a table, first create this function:

CREATE FUNCTION fun_CSV_to_Table
(
@pcsvList varchar(max)
)
RETURNS @tableWithValues table(theColumn varchar(100))
AS
BEGIN 
    DECLARE @pos INT
    WHILE CHARINDEX(',', @pcsvList) > 0
    BEGIN
     SELECT @pos  = CHARINDEX(',', @pcsvList)   

     INSERT INTO @tableWithValues   
     SELECT LTRIM(RTRIM(SUBSTRING(@pcsvList, 1, @pos-1)))

     SELECT @pcsvList = SUBSTRING(@pcsvList, @pos+1, LEN(@pcsvList)-@pos)
    END
    --Insert the last value.
    INSERT INTO @tableWithValues SELECT @pcsvList

    RETURN
END
GO

And then you can use it:

 SELECT SIZES, SUM(QUANTITY)
 FROM TBL_SIZEBREAKUP
 WHERE BRAND=@BRAND
 AND COMBO IN (select theColumn FROM dbo.fun_CSV_to_Table(@aList)) 

/* @aList must be a csv: '1,2,3,...,n' */

You should care about white spaces.

Answer 3

You can use Parameters.AddWithValues (Updated):

DataSet dt = new DataSet();
cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "sp_Accessories";
cmd.Connection = con;

var parameterList = new StringBuilder();
for (int i = 0; i < items.Length; i++)
{
    parameterList.Append(items[i] + ",");
}


var parameters= parameters.ToString().TrimEnd(',');
cmd.Parameters.AddWithValue("@Combo", parameters);
cmd.Parameters.AddRange(
    new SqlParameter[] {
        new SqlParameter("@Mode",mode),
    }}