SQL - Is there a better way of passing a list of keys, for use in a where clause, into a Stored Procedure?

Question

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.

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

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

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.

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?

Manu · Answer 1 · 2009-11-16T15:01:29.467

Use XML for passing arrays to STPs. Example:

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

calling the STP:

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

@Tom: a performance evaluation can be found here

The main lesson is, unless you are willing to use CLR code, XML is the fastest way to go:

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

Have you done any performance comparisons with this method or seen any? It's an interesting approach. — Tom H, Nov 16 '09 at 14:55

score 5 · Answer 2 · answered Nov 16 '09 at 14:52

5

If you're using SQL 2008, you can pass in a table as a parameter to a stored procedure, as explained here

In earlier versions of SQL, the common approaches are either as you already have, or passing in a CSV string to be used within a LIKE clause (I won't demonstrate as I wouldn't particularly recommend the approach).

answered Nov 16 '09 at 14:52

AdaTheDev

142,592
28
206
200

Wouldn't that cause a type cast exception? – djdd87 Nov 16 '09 at 15:00
And, I'm using SQL 2005, not 2008 - see the last paragraph of my question. I'll re-tag as well. – djdd87 Nov 16 '09 at 15:01
Agree regarding use of function in SQL 2005 and earlier. Also agree with the XQuery solution. Table parameters seem awkward to configure and use... are they? – Philip Kelley Nov 16 '09 at 15:06
Apologies, didn't spot the SQL 2005 bit first time round. Personally, I tend use the "function split to table var approach" like you're already doing. The LIKE approach I mentioned would not result in good index usage. The XML approach is an alternative, but personally I don't use it. I don't know what the performance difference would be between XML approach and your current method, you'd need to try both and compare. – AdaTheDev Nov 16 '09 at 15:08

John Gietzen · Accepted Answer · 2012-04-12T02:54:10.837

I use XML lists.

I use this sort of stored procedure:

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

To create this sort of list:

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

I wouldn't use sp_xml_preparedocument in SQL Server 2005+. I'd use nodes() etc — gbn, Nov 16 '09 at 15:02
Blimey, it's a lot harder work that just concatenating a few numbers and calling a function isn't it? +1 for the effort though. — djdd87, Nov 16 '09 at 15:03

SQL - Is there a better way of passing a list of keys, for use in a where clause, into a Stored Procedure?

3 Answers3

Linked