Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server
Article

Passing comma delimited parameter to stored procedure

Rate me:
Please Sign up or sign in to vote.
4.75/5 (17 votes)
13 Feb 20041 min read 143.1K   33   15
How to pass an array of integers to a stored procedure.

Introduction

Referring to an article about passing a comma delimited parameter to a stored procedure by Mr. Dr_X, I've made some revisions to make the whole process simpler.

The problem is passing an array of values (here an array of integers) to a stored procedure and using the values in the body of the stored procedure. For example, suppose that we want to select all employees whose IDs are in an array but the number of elements in the array is not fixed and may change in our application at run time. How can we do this?

A way suggested by Mr. Dr_X is to pass a string of comma delimited integers as a parameter to a stored procedure, like '1,12,56,78'. Our stored procedure is responsible to split the string and extract each value and then use the values in its query.

I've written a user defined function for doing this, which parses the string and puts each integer extracted into a table, then returns the table so the stored procedure can simply use it in its query.

Implementation:

Here is the code:

SQL
Use Northwind

Go
-- @IDs is the string of comma delimited integers 
--  (you can increase the size if you think that your array will be very large)
-- After parsings the string, integers are returned in a table


CREATE Function fnSplitter (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  

Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
 
 -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@IDs)
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return
End

Now we can use it in our stored procedure:

SQL
Use Northwind

Go

CREATE PROCEDURE spSelectEmployees(@IDs Varchar(100)) AS 
Select * From employees Where employeeid In (Select ID From fnSplitter(@IDs))
GO

Now go to query analyzer and enter these:

SQL
Use Northwind 
Exec spSelectEmployees '1,4,5,7,9'

It is essential that your string has a number at start, otherwise the missing value will be evaluated to zero by the parser, for example ,1,3,5 will be output to   0 1 3 5.

Spaces are allowed in the string for example 1 , 3 ,   5 will result in 1 3 5 .

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Iran (Islamic Republic of) Iran (Islamic Republic of)
Software developer and program manager of medical software teams.

Comments and Discussions

 
QuestionHow to pass two or more lists and access values in list individually? Pin
lonilywolf13-Oct-14 23:42
lonilywolf13-Oct-14 23:42 
QuestionThanks Pin
sfloyd25-Apr-13 5:21
sfloyd25-Apr-13 5:21 
GeneralMy vote of 5 Pin
anojoomi10-Jan-13 4:42
anojoomi10-Jan-13 4:42 
GeneralNot working for varchar Pin
satheesh.713421-Jun-07 23:44
satheesh.713421-Jun-07 23:44 
GeneralCopy text Pin
daniel.pitol15-Jun-05 2:09
daniel.pitol15-Jun-05 2:09 
Generalno need for splitter Pin
Joost Breed6-Apr-04 21:23
Joost Breed6-Apr-04 21:23 
With this procedure there is no need for a splitter function. But it might be slow for large tables.

CREATE PROCEDURE spSelectEmployees(@IDs Varchar(100)) AS

set @IDs = ',' + @IDs + ','

Select *
from employees
where @IDs '%,' + ltrim(str(employeeid)) + ',%'

GO

usage is the same

Joost
GeneralRe: no need for splitter Pin
satheesh.713421-Jun-07 23:41
satheesh.713421-Jun-07 23:41 
GeneralAnother one Pin
kuke17-Feb-04 19:33
kuke17-Feb-04 19:33 
GeneralRe: Another one Pin
Reza_Allamehzadeh17-Feb-04 21:21
Reza_Allamehzadeh17-Feb-04 21:21 
GeneralI like mine better... Pin
Oskar Austegard17-Feb-04 6:59
Oskar Austegard17-Feb-04 6:59 
GeneralRe: I like mine better... Pin
sh77717-Feb-04 14:41
sh77717-Feb-04 14:41 
GeneralRe: I like mine better... Pin
JEclipse18-Feb-04 17:08
JEclipse18-Feb-04 17:08 
GeneralYours not working if space is delimiter. Pin
Vadimeti4-Jul-05 9:38
Vadimeti4-Jul-05 9:38 
GeneralRe: Yours not working if space is delimiter. Pin
Oskar Austegard5-Jul-05 2:45
Oskar Austegard5-Jul-05 2:45 
GeneralLength of @IDs Pin
zero.sg16-Feb-04 20:03
zero.sg16-Feb-04 20:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.