SQL - 是否有通过密钥列表，用在where子句中，到存储过程中的一个更好的办法？ [英] SQL - Is there a better way of passing a list of keys, for use in a where clause, into a Stored Procedure?

问题描述

下面的情景;予具有的 CustomerIds 的（1，2，3）已涉及的 OrderIDs的的列表。我有一个存储过程 Delete_OrdersByCustomerIds ，它会删除所有被涉及到订单的 CustomerIds 的规定。

Here's the scenario; I have a list of CustomerIds (1, 2, 3) that have relating OrderIds. I have one Stored Procedure Delete_OrdersByCustomerIds, which deletes all the orders that are related to the CustomerIds specified.

目前，我这样做的方式是连接在 CustomerIds 的成字符串，如1,2,3。我再通过传递这个字符串到我的存储过程，并使用下面的函数来创建一个表廉政局的，我可以加入：

Currently, the way I do this is to concatenate the CustomerIds into a string, i.e. "1,2,3". I then pass this string through to my stored procedure and use the following function to create a Table of Int's that I can join on:

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订单WHERE客户ID国际热核实验堆$ simple_intlist_to_tbl（@CustomerIds）.number 。 （语法可能不是在这里，我想出声来。）

I then just do a DELETE FROM Orders WHERE CustomerId IN iter$simple_intlist_to_tbl(@CustomerIds).number. (The syntax might not be right here, I'm thinking out loud.)

我不禁感慨，这是的坏的。有没有达到同样的事情的一个更好的办法？我不想建立SQL语句我自己，我目前使用的标准.NET ADO包装。

I can't help feeling this is bad. Is there a better way of achieving the same thing? I don't want to build up the SQL statement myself, I'm currently using the standard .Net ADO wrappers.

综观其他问题，即该<一href="http://stackoverflow.com/questions/114504/is-it-possible-to-send-a-collection-of-ids-as-a-ado-net-sql-parameter">one,看来我的方法是可以接受的，所以这是做在SQL 2005的唯一途径？

Looking at other questions, i.e. this one, it seems my method is acceptable, so is this the only way to do it in SQL 2005?

推荐答案

我使用XML列表。

我用这种存储过程：

CREATE PROCEDURE [dbo].[GetUsersInList]
    @UserList nvarchar(MAX)
AS
BEGIN
    DECLARE @DocHandle int
    DECLARE @UsersListTable table (
        [UserID] bigint
    )

    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @UserList

    INSERT INTO
        @UsersListTable ([UserID])
    SELECT
        doc.Value [UserID]
    FROM
        OPENXML(@DocHandle, '/List/Item', 1) WITH ([Value] int) doc

    EXEC sp_xml_removedocument @DocHandle

    SELECT
        *,
        IsNull(cast(TechID as varchar) + ' - ' + DisplayName, DisplayName) [FriendlyName]
    FROM
        dbo.[Users]
    WHERE
        [ID] IN (SELECT [UserID] FROM @UsersListTable)
END

要创建这种列表的：

var list =
    new XElement("List",
        users.Select(user =>
            new XElement("Item", new XAttribute("Value", user.Id))
        )
    );

这篇关于SQL - 是否有通过密钥列表，用在where子句中，到存储过程中的一个更好的办法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！