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

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

CodePudding user response：

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];
}

CodePudding user response：

We might need to pass multiple parameters in SQL IN clause, so we can try to split your input value as below.

I would use create a SqlParameter object instead of using AddWithValue because AddWithValue didn't support declaring type & size.

string sql = "SELECT WorkStatus from viewWorkRequest WHERE WorkStatus in (@Open,@Closed)";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    var para1 = new SqlParameter("@Open",SqlDbType.VarChar,100 );
    para1.Value = "Open";

    var para2 = new SqlParameter("@Closed",SqlDbType.VarChar,100 );
    para2.Value = "Closed";

    cmd.Parameters.Add(para1);
    cmd.Parameters.Add(para2);
    cmd.ExecuteNonQuery();
}