I have 2 tables: Question and Quiz.
Quiz is a collection of Questions.
Quiz table looks like this:
QuizdId | QuestionIds (NVARCHAR50)
1 | 1,2,3,4,5
2 | 6,7,8,9
Question table has primary key ID (integer), Question and Status field
I want to write a procedure to update the status of all the questions in Question Table.
The following Query works in SQL Console
UPDATE questions SET ActiveStatus = 'X'
WHERE FIND_IN_SET(ID, (SELECT QuestionIds from quiz where QuizId = 2)) > 0
4 rows are updated here.
However, when I put the same query in a stored procedure and execute it with parameters, it does not work.
Stored Procedure code:
CREATE DEFINER=`root`@`localhost`
PROCEDURE `update_quiz_status`(IN `QuizId` INT, IN `Status` VARCHAR(1))
MODIFIES SQL DATA
SQL SECURITY INVOKER
UPDATE questions SET ActiveStatus = Status
WHERE FIND_IN_SET(ID, (SELECT QuestionIds from quiz where QuizId = QuizId)) > 0
Procedure has no syntax errors.
When I call the procedure from SQL console, I get the error:
call update_quiz_status(2,'X');
Error : #1242 - Subquery returns more than 1 row
I dont know why the query works in SQL console but not in stored procedure !
Is there any other way I can achieve this ? i.e. update the question status for a given quiz by SQL procedures.
See Question&Answers more detail:
os