Mysql CASE NOT FOUND for CASE STATEMENT on a Stored Procedure

Question

im trying to create a stored procedure that have multiples 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

Everytime im executing this procedure i got the following error is: "Case not found for CASE statement" What im doing wrong?

Is it finding a case that isn't in your WHEN statements? Have you tried adding ELSE into your statements to see if it's falling through anywhere? — Tyler Ferraro, Oct 24 '11 at 20:02

score 29 · Answer 1 · edited Oct 13 '15 at 07:26

This is a MySQL specific problem.
In MySQL, a case must always have a valid match, thus an ELSE clause.
And as the statement after the match cannot be empty, you can fill it up with an empty block like so:

ELSE BEGIN END;

So a valid 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;

score 4 · Answer 2 · answered Oct 24 '11 at 20:11

4

That error means one of the 'case' statements can't find a match. Are you sure variables olds and news contain right values? Try to change last 'when' in each case to 'else' and see if your procedure works then.

answered Oct 24 '11 at 20:11

socha23

10,171
2
28
25

Mysql CASE NOT FOUND for CASE STATEMENT on a Stored Procedure

2 Answers2

Linked