Trouble setting up stored parameter

Question

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.

juergen d · Accepted Answer · 2014-06-20 11:15:06Z

1

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;
    ...

answered Jun 20, 2014 at 11:15

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trouble setting up stored parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related