Mysql CASE NOT FOUND FOR APPLICATION FOR USE OF A COMPLETED PROCEDURE

Question

im trying to create a stored procedure that has multiple CASE STATEMENTS I have the following stored procedure:

BEGIN CASE @olds WHEN 'emp' THEN CASE @news WHEN 'loc' THEN UPDATE equipos SET pe=pe-1,pg=pg+1 WHERE id=@eqloc ; UPDATE equipos SET pe=pe-1,pp=pp+1 WHERE id=@eqvis ; UPDATE partidos SET `eqgan` =@news WHERE id=@mst ; UPDATE log_partidos SET `status` =@news WHERE `match` =@mst ; WHEN 'vis' THEN UPDATE equipos SET pe=pe-1,pg=pg+1 WHERE id=@eqvis ; UPDATE equipos SET pe=pe-1,pp=pp+1 WHERE id=@eqloc ; UPDATE partidos SET `eqgan` =@news WHERE id=@mst ; UPDATE log_partidos SET `status` =@news WHERE `match` =@mst ; END CASE; WHEN 'loc' THEN CASE @news WHEN 'emp' THEN UPDATE equipos SET pe=pe+1,pg=pg-1 WHERE id=@eqloc ; UPDATE equipos SET pe=pe+1,pp=pp-1 WHERE id=@eqvis ; UPDATE partidos SET `eqgan` =@news WHERE id=@mst ; UPDATE log_partidos SET `status` =@news WHERE `match` =@mst ; WHEN 'vis' THEN UPDATE equipos SET pp=pp-1,pg=pg+1 WHERE id=@eqvis ; UPDATE equipos SET pg=pg-1,pp=pp+1 WHERE id=@eqloc ; UPDATE partidos SET `eqgan` =@news WHERE id=@mst ; UPDATE log_partidos SET `status` =@news WHERE `match` =@mst ; END CASE; WHEN 'vis' THEN CASE @news WHEN 'emp' THEN UPDATE equipos SET pe=pe+1,pg=pg-1 WHERE id=@eqvis ; UPDATE equipos SET pe=pe+1,pp=pp-1 WHERE id=@eqloc ; UPDATE partidos SET `eqgan` =@news WHERE id=@mst ; UPDATE log_partidos SET `status` =@news WHERE `match` =@mst ; WHEN 'loc' THEN UPDATE equipos SET pp=pp-1,pg=pg+1 WHERE id=@eqloc ; UPDATE equipos SET pg=pg-1,pp=pp+1 WHERE id=@eqvis ; UPDATE partidos SET `eqgan` =@news WHERE id=@mst ; UPDATE log_partidos SET `status` =@news WHERE `match` =@mst ; END CASE; END CASE; END 

Each time I performed this procedure, I received the following error: "Case not found for CASE statement" What am I doing wrong?

Answer 1

This is a specific MySQL issue.
In MySQL, a case should always have a valid match, so an ELSE clause.
And since the statement after the match cannot be empty, you can fill it with an empty block as follows:

 ELSE BEGIN END; 

Thus, the actual case would be, for example:

 CASE @news WHEN 'emp' THEN UPDATE equipos SET pe=pe+1,pg=pg-1 WHERE id=@eqvis ; WHEN 'loc' THEN UPDATE equipos SET pp=pp-1,pg=pg+1 WHERE id=@eqloc ; ELSE BEGIN END; END CASE; 

Answer 2

This error means that one of the case statements cannot find a match. Are you sure the olds and news variables contain the correct values? Try changing the last “when” in each case to “else” and see if your procedure will work then.