Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
The title indicates you are passing a list of values to a stored procedure (perhaps from a C# app, for example), though the body suggests you just want to filter from a list of data already somewhere in your database. Which is it? –  Nick Chammas Sep 28 '11 at 19:25
    
@Marian: yep, looks like –  gbn Oct 3 '11 at 14:56
add comment

migrated from programmers.stackexchange.com Sep 28 '11 at 17:41

This question came from our site for professional programmers interested in conceptual questions about software development.

marked as duplicate by Nick Chammas, Mark Storey-Smith, Jack Douglas Apr 24 '12 at 13:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

6 Answers

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

share|improve this answer
    
I do this all the time, it's easy and quick. –  Jeremy Holovacs Jan 31 '12 at 15:52
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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
;
share|improve this answer
add comment

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.

share|improve this answer
2  
I prefer using the comma separated var and build a temptable using sommarskog.se/arrays-in-sql-2005.html his approach ;) –  jcolebrand Sep 28 '11 at 19:53
2  
CSVs can work, until you need to put a comma in your string. :) –  mrdenny Sep 29 '11 at 1:48
    
@mrdenny : yeah and then some smart-donkey will devise escaping sequences and then I'll inherit their code base :( –  Andrei Rinea Mar 20 '12 at 11:12
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.