Click here to Skip to main content
Click here to Skip to main content

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.

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:

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:

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:

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

Share

About the Author

Reza_Allamehzadeh

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

Comments and Discussions

 
QuestionThanks Pinmembersfloyd25-Apr-13 5:21 
Thanks, This was just what I needed and works perfectly. Just changed it to Varchar and not Int
Fantastic!
GeneralMy vote of 5 Pinmemberanojoomi10-Jan-13 4:42 
GeneralNot working for varchar Pinmembersatheesh.713421-Jun-07 23:44 
GeneralCopy text Pinmemberdaniel.pitol15-Jun-05 2:09 
Generalno need for splitter PinsussJoost Breed6-Apr-04 21:23 
GeneralRe: no need for splitter Pinmembersatheesh.713421-Jun-07 23:41 
GeneralAnother one Pinsusskuke17-Feb-04 19:33 
GeneralRe: Another one PinmemberReza_Allamehzadeh17-Feb-04 21:21 
GeneralI like mine better... PinmemberOskar Austegard17-Feb-04 6:59 
GeneralRe: I like mine better... Pinmembersh77717-Feb-04 14:41 
GeneralRe: I like mine better... PinsussJEclipse18-Feb-04 17:08 
GeneralYours not working if space is delimiter. PinmemberVadimeti4-Jul-05 9:38 
GeneralRe: Yours not working if space is delimiter. PinmemberOskar Austegard5-Jul-05 2:45 
GeneralLength of @IDs Pinmemberzero.sg16-Feb-04 20:03 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 14 Feb 2004
Article Copyright 2004 by Reza_Allamehzadeh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid