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:
CREATE Function fnSplitter (@IDs Varchar(100) )
Returns @Tbl_IDs Table (ID Int) As
Set @IDs = @IDs + ','
Declare @Pos1 Int
Declare @pos2 Int
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
Set @Pos1 = @Pos1+1
Now we can use it in our stored procedure:
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:
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 .