Passing comma delimited parameter to 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 .