Click here to Skip to main content
12,818,035 members (38,454 online)
Click here to Skip to main content
Add your own
alternative version


33 bookmarked
Posted 13 Feb 2004

Passing comma delimited parameter to stored procedure

, 13 Feb 2004
Rate this:
Please Sign up or sign in to vote.
How to pass an array of integers to a stored procedure.


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.


Here is the code:

Use Northwind

-- @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  

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

Now we can use it in our stored procedure:

Use Northwind


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

Now go to query analyzer and enter these:

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 .


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


About the Author

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

You may also be interested in...

Comments and Discussions

QuestionHow to pass two or more lists and access values in list individually? Pin
lonilywolf14-Oct-14 0:42
memberlonilywolf14-Oct-14 0:42 
QuestionThanks Pin
sfloyd25-Apr-13 6:21
membersfloyd25-Apr-13 6:21 
Thanks, This was just what I needed and works perfectly. Just changed it to Varchar and not Int
GeneralMy vote of 5 Pin
anojoomi10-Jan-13 5:42
memberanojoomi10-Jan-13 5:42 
GeneralNot working for varchar Pin
satheesh.713422-Jun-07 0:44
membersatheesh.713422-Jun-07 0:44 
GeneralCopy text Pin
daniel.pitol15-Jun-05 3:09
memberdaniel.pitol15-Jun-05 3:09 
Generalno need for splitter Pin
Joost Breed6-Apr-04 22:23
sussJoost Breed6-Apr-04 22:23 
GeneralRe: no need for splitter Pin
satheesh.713422-Jun-07 0:41
membersatheesh.713422-Jun-07 0:41 
GeneralAnother one Pin
kuke17-Feb-04 20:33
susskuke17-Feb-04 20:33 
GeneralRe: Another one Pin
Reza_Allamehzadeh17-Feb-04 22:21
memberReza_Allamehzadeh17-Feb-04 22:21 
GeneralI like mine better... Pin
Oskar Austegard17-Feb-04 7:59
memberOskar Austegard17-Feb-04 7:59 
GeneralRe: I like mine better... Pin
sh77717-Feb-04 15:41
membersh77717-Feb-04 15:41 
GeneralRe: I like mine better... Pin
JEclipse18-Feb-04 18:08
sussJEclipse18-Feb-04 18:08 
GeneralYours not working if space is delimiter. Pin
Vadimeti4-Jul-05 10:38
memberVadimeti4-Jul-05 10:38 
GeneralRe: Yours not working if space is delimiter. Pin
Oskar Austegard5-Jul-05 3:45
memberOskar Austegard5-Jul-05 3:45 
GeneralLength of @IDs Pin
zero.sg16-Feb-04 21:03
memberzero.sg16-Feb-04 21: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170308.1 | Last Updated 14 Feb 2004
Article Copyright 2004 by Reza_Allamehzadeh
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid