Search code examples
sqlif-statementstored-proceduresdb2

Why me store procedure just return the result of the first IF?

I'm using this code in SQL

--#SET TERMINATOR @
CREATE PROCEDURE UPDATE_LEADERS_SCORE (
    IN in_School_ID INTEGER, in_Leader_Score INTEGER) 
LANGUAGE SQL 
MODIFIES SQL DATA
  BEGIN
    UPDATE "CHICAGO_PUBLIC_SCHOOLS"
    SET "Leaders_Score" = in_Leader_Score
    WHERE "School_ID" = in_School_ID;
    IF 'in_Leaders_Score' >=  '80' THEN 
        UPDATE "CHICAGO_PUBLIC_SCHOOLS"
        SET "Leaders_Icon" = 'Very_Strong'
        WHERE "School_ID" = in_School_ID;
    ELSEIF 'in_Leaders_Score' >= '60' and 'in_Leaders_Score' <= '79'  THEN
        UPDATE "CHICAGO_PUBLIC_SCHOOLS"
        SET "Leaders_Icon" = 'Strong'
        WHERE "School_ID" = in_School_ID;
    ELSEIF 'in_Leaders_Score' >=  '40' and 'in_Leaders_Score' <=  '59'  THEN
        UPDATE "CHICAGO_PUBLIC_SCHOOLS"
        SET "Leaders_Icon" = 'Average'
        WHERE "School_ID" = in_School_ID;
    ELSEIF 'in_Leaders_Score' >=  '20' and 'in_Leaders_Score' <=  '39'  THEN
        UPDATE "CHICAGO_PUBLIC_SCHOOLS"
        SET "Leaders_Icon" = 'Weak'
        WHERE "School_ID" = in_School_ID;
    ELSE
        UPDATE "CHICAGO_PUBLIC_SCHOOLS"
        SET "Leaders_Icon" = 'Very Weak'
        WHERE "School_ID" = in_School_ID;
        END IF;
  END 
  @

But when a called the procedure and put any value in the second parameter, the row updated just return the string "Very_Strong" in the Leaders Icon column, can some give me a hint?

I already try to make the comparison this mode =>80 put i got this error:

Status: Failed Error message Invalid character found in a character string argument of the function "DECFLOAT".. SQLCODE=-420, SQLSTATE=22018, DRIVER=4.26.14

And I tried to put the 'in_Leaders_Score' with single quotes and I doesn't get error, but the code doesn't make the correct comparisons, and if I put this variable in double or without quotes i got this error: Error message: "LEADERS_SCORE" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.26.14

Solution

  • try to change column lengh by alter column data type as below

    ALTER TABLE CHICAGO_PUBLIC_SCHOOLS
ALTER COLUM LEADERS_ICON SET DATA TYPE VARCHAR(15);