Conditional Statements | Intelligent Enterprises of the 21st Century

Conditional statements allow stored procedures to make decisions. They are used to define multiple branches of execution based on whether or not a condition was met.

A commonly used conditional statement is the IF statement, where a branch of execution can be taken if a specific condition is satisfied. IF statements can also define a branch of execution for when a condition is not met.

Another conditional statement in SQL PL is the CASE statement, which is similar to an IF statement, but the branching decision is more flexible.

The IF Statement

The most commonly used approach for conditional execution is the IF statement. There are essentially three different types of IF statements.

The simplest form of the IF statement does something if a condition is true, and nothing otherwise.

But what happens if you want to do one thing if a condition is true and something else if it is false? This is where the ELSE clause comes in handy. When used in conjunction with an IF statement, you can do something IF a condition is true and something ELSE if the condition is false.

Thirdly, ELSEIF is used to branch to multiple code paths based on mutually exclusive conditions in the same manner as an IF statement. You can make use of an ELSEIF statement to rewrite a ladder of nested IF ... ELSE statements for readability. Your procedure can specify an unlimited number of ELSEIF statements.

The syntax of an IF statement is depicted in Figure 4.7.

Figure 4.7. An IF syntax diagram.

 >>-IF--search-condition-THEN----------------------------------->       .------------------------------.       V                              | >--------SQL-procedure-statement-;---+------------------------->       .------------------------------------------------------------------------       V | >----+--------------------------------------------------------------------+--+>      |                                 .-------------------------------.  |      |                                 V                               |  |      '-ELSEIF--search-condition--THEN-----SQL-procedure-statement--;---+--' >-----+------------------------------------------+--END IF-----><       |        .------------------------------.  |       |        V                              |  |       '-ELSE-----SQL-procedure-statement-;---+---'

The search-condition specifies the condition for which an SQL statement should be invoked. If the condition is false, processing continues to the next search-condition, until either a condition is true or processing reaches the ELSE clause.

SQL-procedure-statement specifies the statements to be invoked if the preceding search-condition is true. If no search-condition evaluates to true, then the SQL-procedure-statement following the ELSE keyword is invoked.

The snippet of an SQL procedure shown in Figure 4.8 demonstrates how the rating of an employee determines the raise in salary and bonus that he or she will receive.

Figure 4.8. An IF statement example.

 CREATE PROCEDURE demo_if ( IN p_rating INT                          , IN p_employee_number char(6) )     LANGUAGE SQL     SPECIFIC demo_if                              -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries di: BEGIN    -- Procedure logic    IF p_rating = 1 THEN                           -- (1)       UPDATE employee          SET salary = salary * 1.10, bonus = 1000        WHERE empno = p_employee_number;    ELSEIF p_rating = 2 THEN                               -- (2)       UPDATE employee          SET salary = salary * 1.05, bonus = 500        WHERE empno = p_employee_number;    ELSE                                                   -- (3)       UPDATE employee          SET salary = salary * 1.03, bonus = 0        WHERE empno = p_employee_number;    END IF; END di

Note

SQL PL does not require the use of a compound statement to execute more than one statement in a branch of a conditional statement.

Tip

Indent statements within the body of IF, ELSEIF, and ELSE statements, in order to improve readability. If there are several levels of nesting, indent code at each level to reflect their level of nesting.

On Line (1), an employee with a 1 rating can expect a raise of 10 percent and a bonus of $1,000. On Line (2), an employee with a 2 rating earns a 5 percent raise with a $500 bonus. On Line (3), all other employees can expect a 3 percent pay hike with no bonus.

You are not simply limited to mathematical operators such as equals (=) and greater than (>). You can also use the SQL keywords NOT, AND, and OR to build conditions in your IF statements.

Note

An IF or ELSEIF condition must involve an operator. It is not sufficient to merely specify a variable (as can be done in some other programming languages), because SQL PL does not support the notion of a negative value meaning false and a positive value meaning true.

Note

Your SQL procedure can also make use of nested IF statements. There is no limit imposed by DB2 on the number of nested levels, though it is best not to get too carried away as it takes away from the readability of your code. Now if that's not enough, you can also nest IF statements inside of loops and loops inside of IF statements.

Tip

When nesting IFs, a common problem is inadvertently matching an ELSE with the wrong IF. Beware.

The CASE Statement

The CASE statement provides the ability to evaluate a list of options based on the value of a single variable. You would most likely choose to use a CASE statement if you have a large decision tree and all branches depend on the value of the same variable. Otherwise, you would be better off using a series of IF, ELSEIF, and ELSE statements. The syntax diagram for the CASE statement is shown in Figure 4.9.

Figure 4.9. A CASE statement syntax diagram.

 >>-CASE----+-| searched-case-statement-when-clause |-+---------->            '-| simple-case-statement-when-clause |---' >----END CASE-------------------------------------------------->< simple-case-statement-when-clause |---expression-------------------------------------------------->       .-------------------------------------------------------------.       |                          .-------------------------------.  |       V                          V                               |  | >--------WHEN--expression--THEN-----SQL-procedure-statement--;---+--+> >-----+------------------------------------------+--------------|       |        .------------------------------.  |       |        V                              |  |       '-ELSE-----SQL-procedure-statement--;---+--' searched-case-statement-when-clause     .-------------------------------------------------------------------.     |                                .-------------------------------.  |     V                                V                               |  | |------WHEN--search-condition--THEN-----SQL-procedure-statement--;---+--+-> >----+------------------------------------------+---------------|      |        .------------------------------.  |      |        V                              |  |      '-ELSE-----SQL-procedure-statement--;---+--'

The CASE statement has two general forms: one that uses a simple-case-statement-when-clause, and another that uses a searched-case-statement-when-clause.

In the simple-case-statement-when-clause, the expression prior to the first WHEN keyword is tested for equality with the value of each expression that follows the WHEN keyword. If the expression results in the same value, the SQL-procedure-statement following the THEN keyword is executed. Otherwise, comparisons are continued between the first expression and the expression following the next WHEN clause. If the result does not match any of the search conditions and an ELSE clause is present, the statements in the ELSE clause are processed.

In a searched-case-statement-when-clause, the search-condition following each WHEN keyword is evaluated. If search-condition evaluates to true, the statements in the associated THEN clause are processed. If it evaluates to false, the next search-condition is evaluated. If no search-condition evaluates to true and an ELSE clause is present, the statements in the ELSE clause are processed.

Both forms of the CASE statement require END CASE to denote the end of the statement.

Note

It is possible to use a CASE statement without an ELSE clause. However, if none of the conditions specified in the WHEN clause are true at runtime, an error will result (SQLSTATE 20000).

The example that you have already seen in Figure 4.8 could be rewritten as shown in Figure 4.10 using the simple-case-statement-when-clause.

Figure 4.10. A simple CASE example.

 CREATE PROCEDURE demo_simple_case ( IN p_rating INT                                   , IN p_employee_number char(6) )     LANGUAGE SQL     SPECIFIC demo_simple_case                     -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries dsc: BEGIN    -- Procedure logic    CASE p_rating       WHEN 1 THEN                                        -- (1) Note: WHEN argument is a value          UPDATE EMPLOYEE             SET SALARY = SALARY *1.10, BONUS = 1000           WHERE EMPNO = p_employee_number;       WHEN 2 THEN                                        -- (2)          UPDATE EMPLOYEE             SET SALARY = SALARY *1.05, BONUS = 500           WHERE EMPNO = p_employee_number;       ELSE          UPDATE EMPLOYEE                                 -- (3)             SET SALARY = SALARY *1.03, BONUS = 0           WHERE EMPNO = p_employee_number;    END CASE; END dsc

Once again, on Line (1) an employee with a rating of 1 can expect a raise of 10 percent and a bonus of $1,000. On Line (2), an employee with a rating of 2 earns a 5 percent raise and a bonus of $500, while on Line (3), all other employees can simply expect a raise of 3 percent and no bonus.

Perhaps there have been some recent changes to the rating system, where there is now a wider range of ratings that employees can receive. Now, two employees with slightly different ratings can earn the same raise and bonus. Obviously, the code needs to be updated.

Figure 4.11 reflects the changes to the rating system and shows how to handle this using a searched-case-statement-when-clause. Note that the WHEN clause now contains a condition.

Figure 4.11. A searched CASE example.

 CREATE PROCEDURE demo_searched_case ( IN p_rating INT                                     , IN p_employee_number char(6) )     LANGUAGE SQL     SPECIFIC demo_searched_case                   -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries dsc: BEGIN    -- Procedure logic    CASE       WHEN p_rating >= 1 AND p_rating < 4 THEN           -- (1)          UPDATE EMPLOYEE             SET SALARY = SALARY *1.10, BONUS = 1000           WHERE EMPNO = p_employee_number;       WHEN p_rating >= 4 AND p_rating < 8 THEN          UPDATE EMPLOYEE             SET SALARY = SALARY *1.05, BONUS = 500           WHERE EMPNO = p_employee_number;       ELSE          UPDATE EMPLOYEE             SET SALARY = SALARY *1.03, BONUS = 0           WHERE EMPNO = p_employee_number;    END CASE; END dsc

As you can see, the code now handles a range of ratings for each condition of the CASE statement. For example, on Line (1), an employee with a rating that falls between 1 and 3 inclusive will receive a raise of 10 percent and a bonus of $1,000.