Question 21634 in Mysql Question posted in
The official MySQL documentation can be found here.

Stored procedure MYSQL

I was finishing advanced IBM Task using MySQL about stored procedures,

The following code cannot be executed , It counters an error in syntax

I have table public_school & column schoolID (should be INT) & column Leaders_Score (should be INT)

I want to update a column Leaders_Icon as it changed with the change of the score itself by using (CASE-END )

THE CODE IS :

DELIMITER @
  
CREATE PROCEDURE UPDATE_LEADERS_SCORE (in_School_ID INT, in_Leader_Score INT) 
BEGIN
    UPDATE public_school
    SET Leaders_Score = in_Leader_Score
    WHERE School_ID = in_School_ID;
    CASE
        WHEN in_Leaders_Score >=80 THEN 
            UPDATE public_school
            SET Leaders_Icon = "Very_Strong"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >= 60 and in_Leaders_Score <= 79  
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Strong"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >=  40 and in_Leaders_Score <=  59
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Average"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >=  20 and in_Leaders_Score <=  39 
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Weak"
            WHERE School_ID = in_School_ID;
        ELSE
            UPDATE public_school
            SET Leaders_Icon = "Very Weak"
            WHERE School_ID = in_School_ID;
    END
END @
  
DELIMITER ;

By the way – MySQL gives error red sign beside — (END @)

I do not know what I have done wrong , or what should I do next

Tags:

2

Answers


  1. 0

    You have to end a CASE statement with END CASE, not just END. And you need a ; after the statement.

    CREATE PROCEDURE UPDATE_LEADERS_SCORE (in_School_ID INT, in_Leader_Score INT) 
BEGIN
    UPDATE public_school
    SET Leaders_Score = in_Leader_Score
    WHERE School_ID = in_School_ID;
    CASE
        WHEN in_Leaders_Score >=80 THEN 
            UPDATE public_school
            SET Leaders_Icon = "Very_Strong"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >= 60 and in_Leaders_Score <= 79  
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Strong"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >=  40 and in_Leaders_Score <=  59
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Average"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >=  20 and in_Leaders_Score <=  39 
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Weak"
            WHERE School_ID = in_School_ID;
        ELSE
            UPDATE public_school
            SET Leaders_Icon = "Very Weak"
            WHERE School_ID = in_School_ID;
    END CASE;
END @
    Login or Signup to reply.
  2. 0

    @Maysara – here is a shorter version without CASE

    DELIMITER @

CREATE PROCEDURE UPDATE_LEADERS_SCORE ( IN in_School_ID INT, IN in_Leader_Score INT) 
BEGIN
    UPDATE public_school
    SET Leaders_Score = in_Leader_Score,
        Leaders_Icon  = ELT( LEAST(( in_Leader_Score div 20 )+1, 5)
        , "Very Weak", "Weak" , "Average", "Strong", "Very_Strong")    
    WHERE School_ID = in_School_ID;
    
END @

DELIMITER ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search