CODEWITHSUNDEEP
sql-serverstored-procedures
Samantha J T Star
Samantha J T Star

Reputation: 32808

How can I loop through a select, call a stored procedure and process the results?

I have a select:

SELECT AnswerGridResponses,
       QuestionUId, 
       UserTestQuestionId
FROM   UserTest,
       UserTestQuestion
WHERE  UserTest.UserTestId = @UserTestId
AND    UserTest.UserTestId = UserTestQuestion.UserTestId

and a procedure I need to call:

EXEC sp_get_correct_responses @QuestionUId, @AnswerGridCorrect output

Can someone give an example of how inside a stored procedure I can call the stored procedure and pass in the @QuestionUId. I have more processing I need to do after it's passed in but I think if I know how to loop then that will give me all I need.

Upvotes: 0

Views: 30

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

Use Cursor to do this. 

  DECLARE CUR CURSOR STATIC FOR
  SELECT DISTINCT QuestionUId
  FROM   UserTest 
  INNER JOIN  UserTestQuestion
  ON UserTest.UserTestId = UserTestQuestion.UserTestId
  WHERE  UserTest.UserTestId = @UserTestId


OPEN vendor_cursor

FETCH NEXT FROM CUR INTO @QuestionUId

WHILE @@FETCH_STATUS = 0
  BEGIN
      EXEC Sp_get_correct_responses
        @QuestionUId,
        @AnswerGridCorrect output

      FETCH NEXT FROM CUR INTO @QuestionUId
  END

CLOSE CUR;

DEALLOCATE CUR;

Note : Cursor may lead to poor performance. Try changing the RBAR operation to set based approach

Upvotes: 2

Related Questions