SELECT CASE and CALL procedures in MYSQL

Question

im trying to do the following

SELECT CASE @st WHEN 'emp' THEN CALL empata(NEW.eqvis) WHEN 'loc' THEN CALL pierde(NEW.eqvis) WHEN 'vis' THEN CALL gana(NEW.eqvis) END INTO @dat; 

But I got this:

 [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CALL empata(NEW.eqvis) WHEN 'loc' THEN CALL pierde(NEW.eqvis) WHEN' at line 18 

But if I delete the "Call", I got "Function db.xxx not found"

What am I doing wrong?

Answer 1

You can convert your empata , pierde and gana pierde into functions and use them as encoded in the first example below:

 -- works SET @st = 'loc'; SELECT CASE @st WHEN 'loc' THEN function_(@st) END INTO @dat; SELECT @dat; 

I tested the following scripts and they did not work:

 -- won't work SET @st = 'loc'; IF @st = 'loc' THEN function_(@st); END IF; -- won't work SET @st = 'loc'; SELECT CASE @st WHEN 'loc' THEN CALL stored_procedure_(@st) END INTO @dat; SELECT @dat; -- won't work SET @st = 'loc'; IF @st = 'loc' THEN CALL stored_procedure_(@st); END IF; 

At least none of the three above instances worked for me. You can try them and see which one works for you.

Also, your question here is the same as: MYSQL Calling stored procedures inside a SELECT CASE on a trigger . Also you sent. I’m not sure which of the moderators will be closed or saved, so I am sending this answer too. If, another time, you would like to follow up on a question that you were asked that didn’t get an answer acceptable to you, just jump so people can see it again.

Answer 2

You cannot use a procedure (using CALL) in this situation, it must be a function .