Syntax error when creating a STORED PROCEDURE on MySQL

Question

I'm trying to create a stored procedure in MySQL and I keep getting a syntax error near line 12. I've already attempted to use END CASE; after the CASE WHEN and the same error pops up. Any advice on what the reason for this error could be? I'm using the wampserver MySQL command line.

Here's the code:

DELIMITER |
CREATE PROCEDURE aumento_sueldo()
    BEGIN
        UPDATE activos
        SET sueldo_issste = sueldo_issste*CASE
        CASE WHEN (YEAR(NOW())-YEAR(fecha_ingreso)) BETWEEN 10 AND 19 THEN 1.01
             WHEN (YEAR(NOW())-YEAR(fecha_ingreso)) BETWEEN 20 AND 29 THEN 1.05
             WHEN (YEAR(NOW())-YEAR(fecha_ingreso)) BETWEEN 30 AND 39 THEN 1.07
             WHEN (YEAR(NOW())-YEAR(fecha_ingreso)) >= 40 THEN 1.10
             ELSE 1.00
END |

Thanks!

still need to end your statements with ;, and also use END for the CASE clause (or END CASE, it's easy to confuse those) — Geoduck
– Geoduck, Commented Feb 16, 2022 at 20:06

Geoduck · Accepted Answer · 2022-02-16 20:09:08Z

1

Try:

DELIMITER //
CREATE PROCEDURE aumento_sueldo()
    BEGIN
        UPDATE activos
        SET sueldo_issste = sueldo_issste * CASE my_case 
                                                WHEN (YEAR(NOW())-YEAR(fecha_ingreso)) BETWEEN 10 AND 19 THEN 1.01 
                                                WHEN (YEAR(NOW())-YEAR(fecha_ingreso)) BETWEEN 20 AND 29 THEN 1.05 
                                                WHEN (YEAR(NOW())-YEAR(fecha_ingreso)) BETWEEN 30 AND 39 THEN 1.07 
                                                WHEN (YEAR(NOW())-YEAR(fecha_ingreso)) >= 40 THEN 1.10 
                                                ELSE 1.00 

                                            END;
END//        
DELIMITER ;

Check here how to use case in store procedure

edited Feb 16, 2022 at 20:09

Geoduck

9,0212 gold badges29 silver badges27 bronze badges

answered Feb 16, 2022 at 20:05

Ergest Basha

9,3064 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Geoduck Over a year ago

too many ;. Should only have it after END CASE

Ergest Basha Over a year ago

@GarrGodfrey I'm pretty sure it needs the comas . Have you checked the manual ?

Geoduck Over a year ago

Yes, and I copy/pasted in Mysql 5.7 to be sure. My edits work

Geoduck Over a year ago

The END vs END CASE always throws me, but only use END CASE if it is a case statement, not in a case expression.

Geoduck Over a year ago

The semi-colons are also specific to case statement vs expression. The case statement is like an IF statement, and then individual statements have semi-colon. But with the expression (ie. part of a SELECT or UPDATE statement), no semi-colons

|

Collectives™ on Stack Overflow

Syntax error when creating a STORED PROCEDURE on MySQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related