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
You have to end a
CASE
statement withEND CASE
, not justEND
. And you need a;
after the statement.@Maysara – here is a shorter version without CASE