CODEWITHSUNDEEP
mysql
lilmocoblaze
lilmocoblaze

Reputation: 13

Syntax error when creating a STORED PROCEDURE on MySQL

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!

Upvotes: 0

Views: 29

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 8973

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

Upvotes: 1

Related Questions