Using parameter for In() not working

Question

I can't seem to get this to work with sql parameters

string itemIds = "86,74,32";

cmd.Parameters.AddWithValue("Items", itemIds);

                    string sql = "SELECT * " +
                                 "FROM Items " +
                                 "WHERE ItemIds IN(@Items)";

Answer 1

You should add each parameter seperately.

string item1 = "86";
string item2 = "32";

cmd.Parameters.AddWithValue("item1", item1);
cmd.Parameters.AddWithValue("item2", item2);

                    string sql = "SELECT * " +
                                 "FROM Items " +
                                 "WHERE ItemIds IN(@item1,@item2)";

This will probably work.

Edit with for loop

string [] numbers = {"12", "23", "34"};
string [] parameters = new string[numbers.Length];
for (int i = 0; i < numbers.Length; i++)
{
     cmd.Parameters.AddWithValue("param"+i, numbers[i]);
     parameters[i] = "@param" + i;
}

var str = String.Join(",", parameters);
string sql = String.Format("SELECT * FROM Items WHERE ItemIds IN({0})",str);

Answer 2

You can't put the whole list of In() arguments in one SQL parameter, you have to put each one in a separate parameter instead.

Here is an elegant way to construct the query string and add the parameters:
Parameterize an SQL IN clause