This is a code snippet for a Stored Procedure which accepts a comma separated values in parameter @Retailers which needs to be stored in a table with different rows. One fine day i had such type of requirement and i fount this post in a forum. I felt like it to share with you.
Declare @MakerBlockID int, @Pos int, @phrase Varchar(4000)
— Insert the maker details
Insert into MakerBlock Values (@Routingnum, @Accountnum, @MinDecAmt, @MaxDecAmt)
— Get the Id from MakerBlock
Select @MakerBlockID = SCOPE_IDENTITY( )
Set @phrase = Replace(@Retailers,’ ‘ ,”)
While Len(@phrase) >0
Begin
Set @pos = CHARINDEX (‘,’,@Phrase)
if @pos > 0
Begin
Insert into MakerBlockRetailers Select @MakerBlockID, Left(@Phrase,@pos-1)
Set @Phrase = Right(@Phrase, Len(@phrase)-@pos)
end
else
Begin
Insert into MakerBlockRetailers Select @MakerBlockID, @Phrase
Set @phrase=”
end
End
Thanx to Say Hey Kid for providing this code.
Original reference: http://www.sqlservercentral.com/Forums/Topic282332-8-1.aspx