Thursday, April 18, 2013

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



 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 ! 

Reactions:

1 comment:

Any Questions or Suggestions ?

About

Professional & Experienced Freelance Developer From India, Technologist, Software Engineer, internet marketer and Open Sources Developer with experience in Finance, Telecoms and the Media. Contact Me for freelancing projects.

Enter your email address:

Delivered by FeedBurner

PHP Freelancer India - Google+