MYSQL Calling stored procedures inside a SELECT CASE on a trigger

Question

im stuck when calling stored procedures inside SELECT CASE on a trigger, it gives me the following error:

[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 'empata(NEW.eqvis)) WHEN 'loc' THEN pierde(NEW.eqvis) WHEN 'vis' THEN g' at line 16 

Here is the code:

 DELIMITER | CREATE TRIGGER updpartido AFTER UPDATE ON partidos FOR EACH ROW BEGIN SET @vgls = vgoles(NEW.eqvis); SET @lgls = vgoles(NEW.eqloc); SET @vglsec = vgolesec(NEW.eqvis); SET @lglsec = vgolesec(NEW.eqloc); SELECT CASE WHEN @ vgls=@lgls THEN "emp" WHEN @vgls>@lgls THEN "loc" WHEN @vgls<@lgls THEN "vis" END INTO @st; 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; SELECT CASE @st WHEN 'emp' THEN CALL empata(NEW.eqloc) WHEN 'vis' THEN CALL pierde(NEW.eqloc) WHEN 'loc' THEN CALL gana(NEW.eqloc) END INTO @dat2; UPDATE equipos SET gf=@vgls , gc=@vglsec WHERE id=NEW.eqvis; UPDATE equipos SET gf=@lgls , gc=@lglsec WHERE id=NEW.eqloc; END; 

|

But if I delete "CALL", triggers are added, but when I do some updating, it gives me the error "FUNCTION not found", because I made them stored procedures, and not as functions, because im was not going to return anything .. .

Any help is much appreciated!

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 they did not work for me.

Answer 2

I am going to adventure the answer, although I am not a MySQL guy:

I do not think that you can call stored procedures inside a select statement whose output is stored in a variable; or more colloquially: you cannot blow and suck at the same time. You either have a select statement whose purpose is to return some records, or to execute some process on your data, but you cannot bind.

If you separate the CALL expressions, I think it should work. You can do the check below into @... and call the corresponding stored procedure.