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?
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;