mysql - UPDATE in Procedure does not work

Question

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.

Answer 1

Your problem is here

SELECT QuestionIds from quiz where QuizId = QuizId

In you mind the second QuizId refers to the passed paramenter. The parser, however, is seeing that as the QuizId in the quiz table and is therefore returning all rows from that table.

Change the name of your parameter and you should be fine.