How to add parameter in SELECT query for fieldName IN @fieldName construction

Question

string idVariable = "qwerty";
string sqlQuery = "select id from user where id = @id";
sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = idVariable;

Adding a value for a specific field is ok.

What if I need to have a few ids and IN in WHERE clause?

List<string> ids = new List<string>{"qwe", "asd", "zxc"};
string sqlQuery = "select id from user where id IN @ids";
sqlCommand.Parameters.Add("@ids", SqlDbType.???).Value = ids;

score 1 · Answer 1 · edited May 23 '17 at 12:06

1

You can't do this directly because the IN operator expcects a list of values, while your parameter is a single value containing a list.

One way to solve it is using a table valued parameter (here is an example), another way is to dynamically create the parameters for the IN as well as the query:

List<string> ids = new List<string>{"qwe", "asd", "zxc"};
string sqlQuery = "select id from user where id IN(";
for(int i=0; i < ids.Count; i++)
{
    sqlQuery += "@Id"+ i + ",";
    sqlCommand.Parameters.Add("@id" + i, SqlDbType.varchar).Value = ids[i];
}
sqlQuery = sqlQuery.TrimEnd(",") + ")";

edited May 23 '17 at 12:06

Community

1
1

answered Aug 13 '15 at 17:11

Zohar Peled

79,642
10
69
121

You're adding multiple parameters to the `SqlCommand` object with the same name (`@ids`). – Thomas Stringer Aug 13 '15 at 17:15
@ThomasStringer good catch! fixed. – Zohar Peled Aug 13 '15 at 17:18

score 0 · Answer 2 · answered Aug 13 '15 at 17:08

You will need to have them added individually.

List<string> ids = new List<string>{"qwe", "asd", "zxc"};
string sqlQuery = "select id from user where id IN (@id1, @id2, @id3)";

sqlCommand.Parameters.Add("@id1", SqlDbType.VarChar).Value = ids[0];
sqlCommand.Parameters.Add("@id2", SqlDbType.VarChar).Value = ids[1];
sqlCommand.Parameters.Add("@id3", SqlDbType.VarChar).Value = ids[2];

score 0 · Answer 3 · answered Aug 13 '15 at 17:13

Yes you cannot dynamically change the nature of the query. You could write an in with a fixed number of choices and add them as parameters, or you could dynamically build SQL string itself to both add the number of @id parameters and the value for it. Since you are already using a SQL string for your command, this sort of dynamic SQL is not a problem. If you were using a stored procedure this would not be as easy, but you could use a stored proc with a bunch of optional parameters and then only pass as many as you need. Of course you could also use Entity Framework and LINQ to build the query logic and let the provider for LINQ to EF build the raw SQL Query.

How to add parameter in SELECT query for fieldName IN @fieldName construction

3 Answers3

Related