SQL IN operator is not working with multiple value of scalar variable in C# Winforms

Question

I have facing a problem that SQL IN operator did not work with multiple value of scalar variables in C#, but query works in SQL Server 2012.

This is working:

SELECT WorkStatus  
FROM viewWorkRequest 
WHERE WorkStatus IN ('Open', 'Closed')

But it's not working in C#

string All = "'Open','Closed'";
string sql = "SELECT WorkStatus FROM viewWorkRequest WHERE WorkStatus IN (@WorkStatus)"

using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.AddWithValue("@WorkStatus", All);
    cmd.ExecuteNonQuery();
}

Thanks in advance

Answer 1

Why would you think that the fact that the first SQL works would have any bearing on the second SQL, which doesn't contain an IN clause?

Each parameter can only contain a single value and each value in an IN clause is distinct, so you cannot pass multiple values for an IN clause with a single parameter. If you don't know how many values you will have then you have to build your SQL in such a way as to allow for a variable number of parameters, e.g.

var values = new[] {"Open", "Closed"};
var sql = $"SELECT * FROM viewWorkRequest WHERE WorkStatus IN ({string.Join(", ", Enumerable.Range(0, values.Length).Select(n => "@WorkStatus" + n))})";
var command = new SqlCommand(sql, connection);

for (var i = 0; i < values.Length; i++)
{
    command.Parameters.Add("@WorkStatus" + i, SqlDbType.VarChar, 50).Value = values[i];
}