How do you submit a List of items to a Stored Procedure to limit results? [duplicate]

Question

Possible Duplicate:
Passing array parameters to a stored procedure

Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read:

WHERE CustomerID in (@CustomerIDs)

Is this possible?

Edit:

To clarify and answer Nick's question, I'd like to pass in a List of values from C# to SQL Server and be able to do a select similar to the SQL I posted above. This would be preferable to getting more data than needed and having to search through it since there is an enormous amount of data.

Answer 1

If you are using SQL 2008 or higher you can use a table valued parameter which would allow you to pass in what is basically an array from the .NET side then use the table variable as an in clause an Nick has said.

If you aren't using SQL 2008 or higher then you'll want to pass in the values as an XML document, then use xquery or OPENXML to parse the XML document and put the values into a temp table (or table variable depending on how many values you expect). Then use the temp table as part of an IN within the WHERE clause.

Answer 2

Alternative approach for pre-SQL2008 is to use a CSV splitter. Jeff Moden has carried out exhaustive testing of various approaches to this.

Your example would become:

DECLARE @CustomerIdList VARCHAR(8000)
SET @CustomerIdList = 
                    'F2D13066-111F-4B85-B85B-2C89F4876D06
                    ,DEF52A31-3272-4296-9C2B-3B3D7DB45BBA
                    ,981BCCF6-D1D2-4F19-B5CF-B4CC4FC6B417'

SELECT
    x, y, z
FROM
    dbo.Customer c
INNER JOIN
    dbo.DelimitedSplit8K(@CustomerIdList, ',') IdList
ON  IdList.Item = c.CustomerId

The DelimitedSplit8K function:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), 
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), 
 cteTally(N) AS (
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

Answer 3

Yes, though you would write it as:

...
WHERE CustomerID IN (
   SELECT CustomerID
   FROM @table_variable
)

Obviously, you can replace what's inside the IN (...) with any query that returns a single column of the appropriate type. This assumes the data you are filtering on is already somewhere in your database. As for how to pass in this data to your stored procedures (per your update), I defer to the other answers here.

This approach works with all versions of SQL Server. (You will need SQL Server 2005+ only if you use a table variable.)

Answer 4

You could populate a temp table prior to calling the proc, and reference the temp table from inside the stored proc. Temp tables are persisted as long as the database connection from your process is open.

If connection pooling is causing issues, you could try creating a global temp table (e.g. ##MyTable). The caveat with that is the table is available to all other processes, so you need to make the name unique to your connection if more than one process will be using it concurrently.

You could alternatively use a perma-temp table. That is a real table, but your process empties it each time it uses it. Again, that table is visible to other instances of your process, so you may have to tag the data appropriately.

Answer 5

You can pass parameters as XML strings in SQL 2005.

This is a really easy method and works perfectly.

DECLARE @productIds xmlSET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'

SELECT
    ParamValues.ID.value('.','VARCHAR(20)') 
FROM 
    @productIds.nodes('/Products/id') as ParamValues(ID) 

Which gives us the following three rows:

3
6
15

For more info see:

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

Answer 6

Getting table valued variables into sprocs from client libraries wasn't possible the last time I looked, although that was quite a long time ago and may have changed. You can write a splitter that takes a string and splits it up, populating a table variable with each item in the list. Then join the table variable against whatever it is that you're querying.