SQL Server : parameter not evaluating in AND part of clause

Question

The following SQL query with parameters works:

select A from B where C = @param1;

C#:

command.Parameters.AddWithValue("@param1", "TEXT");

NOTE: C is a column of string type

The following SQL Query with parameters does not work:

select A from B where C = @param1 and D in (@param2);

C#:

command.Parameters.AddWithValue("@param1", "123,234,456");

NOTE: D is a column of int type

Did you mean `command.Parameters.AddWithValue("@param2", "123,234,456");` ? — Nagaraj Tantri, Sep 29 '14 at 10:58
The `IN (...)` expects a **list of values** - while what you're providing is only a **single string**.... — marc_s, Sep 29 '14 at 11:03

score 4 · Answer 1 · answered Sep 29 '14 at 11:01

Because you can't parameterize an in like that; you are currently asking for an in on a single value that happens to be a string with commas in it - i.e. this will only work if there is a row where D is equal to 123,234,456.

You need to either have a parameter per value i.e.

... and D in (@param2, @param3, @param4)

or use a tool that does this for you. For example, with "dapper":

string c = "TEXT"; 
int[] vals = { 123, 234, 456 };
var data = conn.Query<AType>("select A from B where C = @c and D in @vals",
      new { c, vals }).ToList();

score 0 · Accepted Answer · edited May 23 '17 at 12:13

Please refer link below, they also pass array to SQL IN clause

var parameters = new string[items.Length];
var cmd = new SqlCommand();
for (int i = 0; i < items.Length; i++)
{
    parameters[i] = string.Format("@Age{0}", i);
    cmd.Parameters.AddWithValue(parameters[i], items[i]);
}

cmd.CommandText = string.Format("SELECT * from TableA WHERE Age IN ({0})", string.Join(", ", parameters));
cmd.Connection = new SqlConnection(connStr);

For details please refer: Pass Array Parameter in SqlCommand answered by @Brian

score 0 · Answer 3 · answered Sep 29 '14 at 11:04

It does work. It just doesn't do what you expect. The expression:

D in (@param2)

Is really doing:

D = (@param2)

Because the list consists of only one element. It is an element that contains commas in it, but it is still only one string.

One way around this is to list explicit parameters:

D in (@in1, @in2, @in3, @in4)

Or to use a less efficient method such as like:

',' + @param2 + ',' like '%,' + D + ',%'

Note: this assumes that D is a string type. If a number, then you need to cast it to a character type.

SQL Server : parameter not evaluating in AND part of clause

3 Answers3