CODEWITHSUNDEEP
mysqlloopsstored-procedurescursor
Styphon
Styphon

Reputation: 10447

Trouble setting up stored parameter

I'm new to dealing with stored procedures and I'm trying to set up a complex set of queries to update some statistics for me. The problem is it's saying the syntax is incorrect but I don't know why. It looks like every other example I can find. Can someone please point me in the right direction?

The error I'm getting is a syntax error on this line: FETCH curs INTO @qqId;

My whole code:

DELIMITER //
CREATE PROCEDURE QuestionStatistics(IN quizId INT(11))
BEGIN
  DECLARE bDone INT;
  DECLARE curs CURSOR FOR  SELECT qqId FROM quizQuestions WHERE qqQuizId = quizId;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
  OPEN curs;

  SET bDone = 0;
  REPEAT
    FETCH curs INTO @qqId;  <-- Error here

    SELECT COUNT(*)
    FROM quizAnswers 
    WHERE qaQuizQuestionId = @qqId 
    AND qaIsMarked = 1 
    INTO @totalAnswers;

    SELECT COUNT(*)
    FROM quizAnswers 
    WHERE qaQuizQuestionId = @qqId 
    AND qaCorrect = 1 
    INTO @totalCorrect;

    SELECT SUM(qaValue)
    FROM quizAnswers 
    WHERE qaQuizQuestionId = @qqId 
    AND qaIsMarked = 1 
    INTO @totalValue;

    UPDATE quizQuestions
    SET qqAveragePoints = ROUND(@totalValue / @totalAnswers, 2),
      qqPercentageCorrect = ROUND(100 * @totalCorrect / @totalAnswers)
  WHERE qqId = @qqId;
  UNTIL bDone END REPEAT;

  CLOSE curs;
END//
DELIMITER ;

My original inspiration for this code was based off of the code in this answer here.

Upvotes: 0

Views: 21

Answers (1)

juergen d
juergen d

Reputation: 204924

Declare a local variable for the cursor and not a user-defined variable (with @)

DELIMITER //
CREATE PROCEDURE QuestionStatistics(IN quizId INT(11))
BEGIN
  declare qqIdVar INT;
  ...
  REPEAT
    FETCH curs INTO qqIdVar;  

    SELECT COUNT(*)
    FROM quizAnswers 
    WHERE qaQuizQuestionId = qqIdVar
    AND qaIsMarked = 1 
    INTO @totalAnswers;
    ...

Upvotes: 1

Related Questions