Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing a list of values to a Stored Procedure

T-SQL Hints and Tips

Passing a list of values to a Stored Procedure

by  JamesLean  Posted    (Edited  )
This requirement is one which has popped up countless times in the forum:

How can I pass a comma-delimited list of values into a stored procedure and return matching rows from a table?

If you were writing a static query it would probably look something like this:

Code: 
SELECT * FROM t1
WHERE c1 IN (1, 2, 3)

However, if you try and write a stored proc and substitute a simple variable in for the list, you'll find that it will probably throw an error, if not it certainly won't return the correct rows:

Code: 
CREATE PROC myproc
  @list varchar(1000)
AS

SELECT * FROM t1
WHERE c1 IN (@list)

So what can you do?

Option 1 - Dynamic SQL

The first option, and probably the simplest, is to use dynamic SQL. This involves creating a SQL query string by concatenating the @list variable and using the EXEC() function to execute it:

Code: 
CREATE PROC myproc
  @list varchar(1000)
AS

EXEC('SELECT * FROM t1 WHERE c1 IN (' + @list + ')')

This will work when passing in a list of integer values ('1,2,3') but if you want to pass character data ('abc,def,gh') you need to do some extra coding to add in quotes around every value.

Using dynamic SQL is also not ideal as you lose a lot of the benefits of stored procs, eg pre-compilation, security (ownership chains) etc).

Option 2 - Table-valued Function

If you're using SQL 2000 then Microsoft have written a user-defined function that will take a list of values and return a table variable with each value on a seperate row. You can then use this to JOIN to your table and retrieve matching rows. You can get the code for the UDF and full documentation here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp

Option 3 - Temp Table

If you are using a version of SQL Server before 2000 then you can take the basic premise behind the function but use a temporary table within your stored proc. I have written an example below which should illustrate the method:

Code: 
CREATE PROC apGetList
  @list varchar(1000)
AS

SET NOCOUNT ON

DECLARE @pos int

--create table to hold parsed values
CREATE TABLE #list (val varchar(10))

--add comma to end of list
SET @list = @list + ','

--loop through list
WHILE CHARINDEX(',', @list) > 0
BEGIN
  --get next comma position
  SET @pos = CHARINDEX(',', @list)

  --insert next value into table
  INSERT #list VALUES (LTRIM(RTRIM(LEFT(@list, @pos - 1))))

  --delete inserted value from list
  SET @list = STUFF(@list, 1, @pos, '')
END

--now get data from your table using JOIN to temp table
SELECT col1, col2
FROM table1 t1 JOIN #list t2 ON t1.col1 = t2.val

You can execute this SP and pass in a list of any values, character or numeric:

Code: 
EXEC apGetList '123,test,a1b2c'

Just remember to change the data type of the temp table column to match the types you are passing.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top