problems with a stored prc

[Phil Townsend]

I have to get a stored procedure to produce a result set then loop
through it and insert records into another table in the same db. What I
have written is causing SQL server to hang or some other performance
bottleneck. Here is the code. Anybody got any ideas? Thanks!

ALTER PROCEDURE initQuestionsAnswered
@empid int,@testid int
AS

declare @ANSID VARCHAR(10),@QID VARCHAR(10)
DECLARE MY_CURS1 CURSOR FOR
select ansid,answers.questionid from
answers inner join questions on
questions.questionid=answers.questionid
inner join concepts on
concepts.conceptid=questions.conceptid
where concepts.testid=@testid

OPEN MY_CURS1

FETCH MY_CURS1 INTO @ANSID,@QID
WHILE (@@FETCH_STATUS=0)
BEGIN
if not exists(
select answerid from questionsanswered where questionid=@QID and
empid=@empid)

BEGIN
insert into questionsanswered
(empid,questionid,testid,answercorrect)
values (@empid,@QID,@testid,0)
END

END
close my_curs1

 

[Guest]

You aren't fetching next in your cursor. Hence, you are constantly looping
over the same record.


Try this:
FETCH MY_CURS1 INTO @ANSID,@QID
WHILE (@@FETCH_STATUS=0)
BEGIN
if not exists(
select answerid from questionsanswered where questionid=@QID and
empid=@empid)

BEGIN
insert into questionsanswered(empid,questionid,testid,answercorrect)
values (@empid,@QID,@testid,0)
END

FETCH NEXT FROM MY_CURS1
INTO @ANSID,@QID
END
close my_curs1


See example B at
http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_90rs.asp.