Geek asks and answers

SQL Is there a better way to pass a list of keys for use in the where clause to a stored procedure?

Here is the script; I have a list of CustomerIds (1, 2, 3) that are associated with OrderIds. I have one stored procedure Delete_OrdersByCustomerIdsthat deletes all orders associated with the specified CustomerIds.

Currently, the way I do this is to combine CustomerIds into a string, that is, "1,2,3". Then I pass this line to my stored procedure and use the following function to create an Int table that I can join:

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

Then I just do it DELETE FROM Orders WHERE CustomerId IN iter$simple_intlist_to_tbl(@CustomerIds).number. (The syntax may not be here, I think out loud.)

, . ? SQL, .Net ADO-.

, , , , SQL 2005?

+1
4

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

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:

: CLR, XML - :

XML 40-60% , CLR, , . , XML - , - .

+6

SQL 2008, ,

SQL , CSV-, LIKE ( , ).

+5

More articles:


All Articles