How to pass sqlparameter to IN()?

Question

For some reason the Sqlparameter for my IN() clause is not working. The code compiles fine, and the query works if I substitute the parameter with the actual values

StringBuilder sb = new StringBuilder();
            foreach (User user in UserList)
            {
                sb.Append(user.UserId + ",");
            }

            string userIds = sb.ToString();
            userIds = userIds.TrimEnd(new char[] { ',' });


SELECT userId, username 
FROM Users 
WHERE userId IN (@UserIds) 

Answer 1

You have to create one parameter for each value that you want in the IN clause.

The SQL needs to look like this:

SELECT userId, username 
FROM Users 
WHERE userId IN (@UserId1, @UserId2, @UserId3, ...) 

So you need to create the parameters and the IN clause in the foreach loop.
Something like this (out of my head, untested):

StringBuilder sb = new StringBuilder();
int i = 1;

foreach (User user in UserList)
{
    // IN clause
    sb.Append("@UserId" + i.ToString() + ",");

    // parameter
    YourCommand.Parameters.AddWithValue("@UserId" + i.ToString(), user.UserId);

    i++;
}

Answer 2

Possible "cleaner" version:

StringBuilder B = new StringBuilder();
for (int i = 0; i < UserList.Count; i++)
     YourCommand.Parameters.AddWithValue($"@UserId{i}", UserList[i].UserId);
B.Append(String.Join(",", YourCommand.Parameters.Select(x => x.Name)));

Answer 3

If you are using SQL 2008, you can create a stored procedure which accepts a Table Valued Parameter (TVP) and use ADO.net to execute the stored procedure and pass a datatable to it:

First, you need to create the Type in SQL server:

CREATE TYPE [dbo].[udt_UserId] AS TABLE(
    [UserId] [int] NULL
)

Then, you need to write a stored procedure which accepts this type as a parameter:

CREATE PROCEDURE [dbo].[usp_DoSomethingWithTableTypedParameter]
(
   @UserIdList udt_UserId READONLY
)
AS
BEGIN

        SELECT userId, username 
        FROM Users 
        WHERE userId IN (SELECT UserId FROM @UserIDList) 

END

Now from .net, you cannot use LINQ since it does not support Table Valued Parameters yet; so you have to write a function which does plain old ADO.net, takes a DataTable, and passes it to the stored procedure: I've written a generic function I use which can do this for any stored procedure as long as it takes just the one table-typed parameter, regardless of what it is;

    public static int ExecStoredProcWithTVP(DbConnection connection, string storedProcedureName, string tableName, string tableTypeName, DataTable dt)
    {
        using (SqlConnection conn = new SqlConnection(connection.ConnectionString))
        {
            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter p = cmd.Parameters.AddWithValue(tableName, dt);
            p.SqlDbType = SqlDbType.Structured;
            p.TypeName = tableTypeName;

            conn.Open();
            int rowsAffected = cmd.ExecuteNonQuery(); // or could execute reader and pass a Func<T> to perform action on the datareader;
            conn.Close();

            return rowsAffected;
        }
    }

Then you can write DAL functions which use this utility function with actual names of stored procedures; to build on the example in your question, here is what the code would look like:

    public int usp_DoSomethingWithTableTypedParameter(List<UserID> userIdList)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("UserId", typeof(int));

        foreach (var userId in updateList)
        {
            dt.Rows.Add(new object[] { userId });
        }

        int rowsAffected = ExecStoredProcWithTVP(Connection, "usp_DoSomethingWithTableTypedParameter", "@UserIdList", "udt_UserId", dt);
        return rowsAffected;
    }

Note the "connection" parameter above - I actually use this type of function in a partial DataContext class to extend LINQ DataContext with my TVP functionality, and still use the (using var context = new MyDataContext()) syntax with these methods.

This will only work if you are using SQL Server 2008 - hopefully you are and if not, this could be a great reason to upgrade! Of course in most cases and large production environments this is not that easy, but FWIW I think this is the best way of doing this if you have the technology available.

Answer 4

SQL Server sees your IN clause as:

IN ('a,b,c')

What it needs to look like is:

IN ('a','b','c')

There is a better way to do what you're trying to do.

• If the user id's are in the DB, then the IN clause should be changed to a subquery, like so:

  IN (SELECT UserID FROM someTable WHERE someConditions)

• This is a hack -- it doesn't work well with indexes, and you have to be careful it works right with your data, but I've used it successfully in the past:

  @UserIDs LIKE '%,' + UserID + ',%' -- also requires @UserID to begin and end with a comma