INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Passing a list of values to a Stored Procedure by JamesLean
Posted: 5 Aug 03

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:

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:

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:

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:

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:

EXEC apGetList '123,test,a1b2c'

Just remember to change the data type of the temp table column to match the types you are passing.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close