if object_id( 'dbspBendAnswer') Is NOT NULL
drop procedure dbspBendAnswer
go
create procedure dbspBendAnswer
/*
Name: dbspBendAnswer
Description:
   Transforms answers in the Answer table from x,y,z format
to row based data in NewAnswer.

Author: Steve Jones

Modifications:
--------------
*/
as
set nocount on
declare r_curs cursor for
select answerid, userid, questionid, answer
   from answer
declare @r int, @u int, @q int, @a varchar( 20), @c int

open r_curs
fetch next from r_curs into @r, @u, @q, @a

/*
Loop through the rows
*/
while @@fetch_status = 0
begin
  /*
    Get the location of the comma and insert the first
    answer into NewAnswer. If there is no comma, then
    there is only one answer and we insert the entire thing.
  */
  select @c = charindex( ',', @a)
  if @c > 0
    insert NewAnswer( userid, questionid, Answer)
      select @u, @q, substring( @a, 1, @c - 1)
  else
    insert NewAnswer( userid, questionid, Answer)
      select @u, @q, @a

  /*
    Loop within this row until there are no more commas (and
    hence, this is the last answer). Insert each into NewAnswer
    and then remove the answer and comma from the string.
  */
  while @c > 0
   begin
    select @a = substring( @a, @c + 1, len( @a))
    select @c = charindex( ',', @a)
    if @c > 0
      insert NewAnswer( userid, questionid, Answer)
        select @u, @q, substring( @a, 1, @c - 1)
    else
      insert NewAnswer( userid, questionid, Answer)
        select @u, @q, @a
   end
  fetch next from r_curs into @r, @u, @q, @a
end

/*
Clean up
*/
deallocate r_curs

return
go
if object_id( 'dbspBendAnswer') Is NOT NULL
  select 'dbspBendAnswer Created'
else
  select 'Error:dbspBendAnswer NOT Created'
go