CODEWITHSUNDEEP
sqlstored-proceduresdb2
Marc Intes
Marc Intes

Reputation: 737

Stored Procedure - table column name as variable

I have here a stored procedure that calls table columns from the sample database, but my problem is I wanted to make one column name into a variable so that i could use it in a condition. My code does not work but I have no clue on how to make it as a variable.

I want to make the column name SALARY as a variable.

Here is my code:

CREATE PROCEDURE TAXEXEMPT ()

    DYNAMIC RESULT SETS 1
    LANGUAGE SQL

BEGIN

    DECLARE display CURSOR WITH RETURN TO CLIENT FOR

        SELECT EMPNO, LASTNAME, SALARY, SALARY AS "TAX EXEMPTION" 
        FROM EMPLOYEE 
        WHERE WORKDEPT BETWEEN 'C01' AND 'D21';

        IF ( SALARY < 15001 ) THEN
            --DO NOTHING
        ELSEIF ( SALARY >= 15001 OR SALARY <= 22000 ) THEN
            UPDATE EMPLOYEE SET "TAX EXEMPTION" = SALARY * 1.03;
        ELSEIF ( SALARY >= 22001 OR SALARY <= 32000 ) THEN
            UPDATE EMPLOYEE SET "TAX EXEMPTION" = (SALARY * 1.05) + 100;
        ELSEIF ( SALARY >= 32001 OR SALARY <= 45000 ) THEN
            UPDATE EMPLOYEE SET "TAX EXEMPTION" = (SALARY * 1.07) + 200;
        ELSE
            --DO NOTHING
        END IF; 

    OPEN display;

END

Any kind of help will do, thanks in advance.

Upvotes: 0

Views: 778

Answers (1)

mustaccio
mustaccio

Reputation: 19001

What you want is a CASE expression:

DECLARE display CURSOR WITH RETURN TO CLIENT FOR

         SELECT EMPNO, LASTNAME, SALARY, 
                   CASE 
                     WHEN SALARY < 15001 THEN SALARY 
                     WHEN SALARY BETWEEN 15001 AND <= 22000 THEN SALARY * 1.03
                     WHEN SALARY BETWEEN 20001 AND <= 32000 THEN (SALARY * 1.05) + 100
                     ...
                     ELSE SALARY
                   END AS "TAX EXEMPTION" 
         FROM EMPLOYEE 
         WHERE WORKDEPT BETWEEN 'C01' AND 'D21';

     OPEN display;

Upvotes: 3

Related Questions