如何将sqlparameter传递给IN()函数？

Question

因为某种原因，我的IN()语句的Sqlparameter不起作用。代码编译良好，如果我用实际值替换参数，则查询有效。

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) 

- chobo

---

逗号必须在字符串之间，而不是在字符串内部。 - John Pick

逗号分隔每个用户ID。 - chobo

http://www.sommarskog.se/arrays-in-sql-2005.html - Tim Schmelter

Answer 1

如果你想在 IN 子句中使用多个值，就必须为每个值创建一个参数。

SQL 语句应该像这样：

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

所以您需要在foreach循环中创建参数和IN子句。
类似这样（我想出来的，未经测试）：

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

- Christian Specht

---

请记得删除末尾的逗号，因为在上面的例子中会出现(@userId,)。 - Wolf

虽然这是一篇很老的帖子，但我还是想更新一下以回答更多人的问题。为了帮助更多的谷歌用户，你可以将XML作为参数来实现类似CSV和IN子句查询的效果 - SELECT userId, username FROM Users u1 INNER JOIN @UsersID.nodes('/ID') T(col) ON u1.userId = t.col.value('.', 'int') - 0cool

对于任何使用此功能的人来说，这是一个巨大的警告。SQL Server默认将参数的最大数量设置为2100。 - SixOThree

Answer 2

可能的“简化”版本：

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)));

- mrogunlana

---

我不明白。当你执行YourCommand.Parameters.Select(x => x.Name)时，你得到的列表是像这样的：John，Mary，...而不是User1，User2，User3的列表。 - LucaCosta

@LucaCosta，你因为使用字符串插值得到+1分，因为吹毛求疵我的“干净”代码而扣去了-1分，哈哈开玩笑，做得好 :) - mrogunlana

Answer 3

如果你正在使用SQL 2008，你可以创建一个接受表值参数（TVP）的存储过程，并使用ADO.net执行存储过程并将数据表传递给它：

首先，在SQL服务器中，你需要创建该类型：

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

接下来，您需要编写一个存储过程，该存储过程接受此类型作为参数：

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

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

END

现在从.net开始，由于它还不支持表值参数，您不能使用LINQ；因此，您必须编写一个函数，该函数执行纯老式的ADO.net，接受一个DataTable，并将其传递给存储过程：我编写了一个通用函数，可以为任何存储过程执行此操作，只要它仅采用一个表类型参数，而不管它是什么;

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

接下来，您可以编写使用此实用程序函数和存储过程的实际名称的DAL函数；以您问题中的示例为基础，以下是代码示例：

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

请注意上面的“connection”参数 - 我实际上在部分DataContext类中使用这种类型的函数来扩展LINQ DataContext与我的TVP功能，并且仍然使用(using var context = new MyDataContext())语法来调用这些方法。

只有当您使用SQL Server 2008时，此方法才有效 - 希望您正在使用，如果没有，这可能是升级的一个很好的理由！当然，在大多数情况下和大型生产环境中，这并不容易，但值得一提的是，如果您拥有可用的技术，我认为这是最佳方法。

- Dmitriy Khaykin

---

如果大多数表格使用相同类型的主键，您是否可以创建一个通用可重复使用的ID参数UDT？例如：CREATE TYPE [dbo].[udt_IntId] AS TABLE([Id] [int] NULL)以便在任何需要对整数主键ID执行SQL IN子句的情况下重复使用。 - Pxtl

Answer 4

SQL Server把您的IN语句看作：

IN ('a,b,c')

需要呈现的样子是：

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

有一种更好的方法可以实现你想要做的事情。

• 如果用户ID在数据库中，则应将IN子句更改为子查询，如下所示：

  IN (SELECT UserID FROM someTable WHERE someConditions)

• 这是一种hack方法 - 它与索引不兼容，你必须小心确保它在你的数据中正确工作，但我过去曾成功使用过这种方法：

  @UserIDs LIKE '%,' + UserID + ',%' -- 还需要@UserID以逗号开头和结尾

- John Pick

---

你的黑客技巧很棒。即使它可能会强制进行完整扫描并阻止优化器发挥作用，但这是一个聪明的技巧，也适用于Access。 - iDevlop

@John：我已经尝试过这个：IN (@param)，然后 command.Parameters.AddWithValue("@param", "'a','b','c'"); 但是没有成功。你能给予一些建议吗？ - Praveen

如果你总是有3个参数，那么你可以使用IN (@param1, @param2, @param3)，然后使用command.Parameters.AddWithValue("@param1", "a"); command.Parameters.AddWithValue("@param2", "b"); command.Parameters.AddWithValue("@param3", "c");。如果你不总是有3个值，也许你应该提出一个新的stackoverflow.com问题，提供足够的细节，并指向我新的问题。我敢打赌，几个人会立即尝试回答。 - John Pick

@John：谢谢。但我找到了一个简单的方法，它完美地工作了 string param="'a','b','c'"; 然后 "SELECT * FROM table WHERE IN (" + param + ")";。请问这是否是正确的方法。 - Praveen

如果a、b和c是用户输入的话，那么你的代码容易受到SQL注入攻击。因此，你应该使用Parameter对象来代替。如果想进一步讨论这个问题，请在stackoverflow.com上创建一个新的问题。 - John Pick