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
Begin

--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+','),'')

End


No comments:

Post a Comment