SP to insert a list

I am attempting to write a stored procedure to insert multiple records based on a list stored in a string (varchar). The first parameter is the an ID number and the second parameter is the list (ie "1,2,4,6").  I would like suggestion on either parsing the list then running many insert of the type INSERT table (ID,Value) VALUES (@Id,@Value) or can I do a batch insert with nested selects.
amnhAsked:
Who is Participating?
 
hakyemezCommented:
/*@yourstring = '1,2,4,6'*/
declare @id int
declare @value int
declare @pos int,@newstr varchar(255),@bExit int
select @pos=CharIndex(",",@yourstring)
select @id=Convert(int,SubString(@yourstring,1,@pos-1))
select @newstr=SubString(@yourstring,@pos+1,datalength(@yourstring)-@pos)
select @bExit=-1
while bExit=-1
begin
    select @pos=Charindex(",",@newstr)
    if @pos<>0
    begin
        select @value = Convert(int,SubString(@newstr,1,@pos-1))
        insert into table (Id,Value) values (@id,@value)
        select @newstr = SubString(@newstr,@pos+1,datalength(@newstr)-@pos)
    end
    else select bExit=0
end



0
 
amnhAuthor Commented:
Good Answer.
I changed slightly by the passing the @id as seperate parameter.
@ID integer,
@yourstring varchar(255)
as
declare @value int
declare @pos int,@newstr varchar(255),@bExit int
select @pos=CharIndex(',',@yourstring)
select @newstr=@yourstring
select @bExit=-1
while @bExit=-1
begin
      select @pos=Charindex(',',@newstr)
      if @pos<>0
      begin
            select @value = Convert(int,SubString(@newstr,1,@pos-1))
            insert into tblUserListMem (User_ID,UserList_ID) values (@id,@value)
            select @newstr = SubString(@newstr,@pos+1,datalength(@newstr)-@pos)
      end
      else
      begin
            select @bExit=0
            select @value = Convert(int,@newstr)
            insert into table (User_ID,UserList_ID) values (@id,@value)
      end
end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.