c# - SQL - 是否有更好的方法将用于 where 子句的键列表传递到存储过程中？

Question

这是场景；我有一个具有相关 OrderIds 的 CustomerIds 列表(1、2、3)。我有一个存储过程 Delete_OrdersByCustomerIds，它删除与指定的 CustomerIds 相关的所有订单。

目前，我这样做的方法是将 CustomerIds 连接成一个字符串，即“1,2,3”。然后我将这个字符串传递到我的存储过程并使用以下函数创建一个我可以加入的 Int 表:

CREATE FUNCTION [dbo].[iter$simple_intlist_to_tbl] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
  RETURN
END

然后我只执行 DELETE FROM Orders WHERE CustomerId IN iter$simple_intlist_to_tbl(@CustomerIds).number。 (语法可能不在这里，我正在大声思考。)

我忍不住觉得这很不好。有没有更好的方法来实现同样的事情？我不想自己构建 SQL 语句，我目前正在使用标准的 .Net ADO 包装器。

查看其他问题，即这个 one ，看来我的方法是可以接受的，那么这是在 SQL 2005 中唯一的方法吗？

Answer 1

使用 XML 将数组传递给 STP。示例:

CREATE PROC myProc
(
    @list AS XML
)
AS
(
    SELECT items.item.value('.', 'VARCHAR(MAX)') AS Item
    FROM @list.nodes('list/item') items(item)
)

调用 STP:

 myProc '<list><item>a</item><item>b</item></list>'

@Tom:可以找到性能评估here

主要的教训是，除非您愿意使用 CLR 代码，否则 XML 是最快的方法:

In my tests, the execution times for XML are 40-60 % higher than for the CLR, but it is twice as fast as the iterative method. In fact, XML is the fastest method that does not require any preparations in the server