how to use comma - separated ids as a parameter in Store Procedure - MSSQL

how to use comma - separated ids as a parameter in Store Procedure - MSSQL

 

According to my knowledge if we wish to use comma separated ids in store procedure we need to make a function IdsToTable - which will take ids as a string parameter and return table, this table we can use in IN( ) statement of 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
Use Northwind

Go

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

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 . 


Hope you have enjoyed to read this post,
let me know if you have more suitable suggestions.

you might be more interested in MSSQL, StoreProcedure, TIPS & TRICKS


Good Day [/ Night] ! Happy Google + ing !