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

CodePudding user response：

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 @