Reputation: 10447
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
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