skip to Main Content

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

2

Answers


  1. 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. @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