Search This Blog

Tuesday, April 17, 2012

Looping through list in SQL

If we have a comma separated list and the requirement is to loop through the list of items one by one or to pass these items to a stored procedure. The easiest and simple way to do this is given below with coments.

--comma separated list
Declare @S varchar(20)
set @S ='abc,def,ghi,jkl' 

--loop through each item
while len(@S) > 0

--get the first item form the list
declare @S1 varchar(20)
set @S1 = left(@S, charindex(',' , @S+',')-1)

--Print it
Print @S1

--Delete the item from the list
set @S = Stuff(@S,1,charindex(',',@S+','),'')


No comments:

Post a Comment