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?

Answer 1

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

Answer 2

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

Answer 3

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 consume this sort of list:

XmlDocument doc = new XmlDocument();
doc.LoadXml("<List></List>");

foreach (var user in users)
{
    XmlElement item = doc.CreateElement("Item");
    XmlAttribute value = doc.CreateAttribute("Value");
    value.Value = user.Id.ToString();
    item.Attributes.Append(value);
    doc.DocumentElement.AppendChild(item);
}

Answer 4

I use a table-valued function for this purpose.

Two blog posts (and follow-up dialog) that might be useful:

http://sqlblog.com/blogs/aaron%5Fbertrand/archive/2009/08/01/processing-a-list-of-integers-my-approach.aspx

http://sqlblog.com/blogs/aaron%5Fbertrand/archive/2009/08/06/more-on-splitting-lists-custom-delimiter-preventing-duplicates-and-maintaining-order.aspx

The difference between my function and yours is that mine uses a numbers table and therefore is not a multi-statement TVF.