具有可变参数数量的存储过程

Question

我有存储过程，我必须传递参数，但问题是我不确定要传入多少个参数，它可以是 1，下次运行它可以是 5。

 cmd.Parameters.Add(new SqlParameter("@id", id)

谁能帮助我如何在存储过程中传递这些可变数量的参数？谢谢

原文由 CPDS 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以将它作为逗号分隔的列表传递，然后使用拆分函数，并加入结果。

 CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN
   (
       SELECT Item = CONVERT(INT, Item)
       FROM
       (
           SELECT Item = x.i.value('(./text())[1]', 'INT')
           FROM
           (
               SELECT [XML] = CONVERT(XML, '<i>'
                    + REPLACE(@List, @Delimiter, '</i><i>')
                    + '</i>').query('.')
           ) AS a
           CROSS APPLY
           [XML].nodes('i') AS x(i)
       ) AS y
       WHERE Item IS NOT NULL
   );

现在你的存储过程：

 CREATE PROCEDURE dbo.doStuff
    @List VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cols FROM dbo.table AS t
        INNER JOIN dbo.SplitInts(@List, ',') AS list
        ON t.ID = list.Item;
END
GO

然后调用它：

 EXEC dbo.doStuff @List = '1, 2, 3, ...';

您可以在此处查看一些背景、其他选项和性能比较：

• 以正确的方式拆分字符串 - 或次佳方式
• 拆分字符串：后续行动
• 拆分字符串：现在使用更少的 T-SQL
• 比较字符串拆分/连接方法
• 处理整数列表：我的方法
• 拆分整数列表：另一个综述
• 有关拆分列表的更多信息：自定义分隔符、防止重复和维护顺序
• 从 SQL Server 中的字符串中删除重复项

但是，在 SQL Server 2016 或更高版本上，您应该查看 STRING_SPLIT() 和 STRING_AGG() ：

• 性能惊喜和假设：STRING_SPLIT()
• SQL Server 2016 中的 STRING_SPLIT()：后续 #1
• SQL Server 2016 中的 STRING_SPLIT()：后续 #2
• SQL Server v.Next：STRING_AGG() 性能
• 使用 SQL Server 的新 STRING_AGG 和 STRING_SPLIT 函数解决老问题

原文由 Aaron Bertrand 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

您可以执行此操作的另一种方法是将参数序列化为 JSON 字符串并将其传递到您的存储过程。您可以使用 NewtonSoft.JSON 包执行以下操作： string json = JsonConvert.SerializeObject(obj); 并将 json var 传递到您的 proc 中。

原文由 Mox_z 发布，翻译遵循 CC BY-SA 4.0 许可协议