CASE statement

The CASE statement selects an execution path based on multiple conditions.

Syntax

simple-when-clause

searched-when-clause

else-clause

Description

label: Specifies the label for the CASE statement. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to SQL labels.
simple-when-clause: The value of 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 comparison is true, the statements in the associated THEN clause are executed and processing of the CASE statement ends. If the result is unknown or false, processing continues to the next comparison. If the result does not match any of the comparisons, and an ELSE clause is present, the statements in the ELSE clause are executed.
searched-when-clause: The search-condition following the WHEN keyword is evaluated. If it evaluates to true, the statements in the associated THEN clause are executed and processing of the CASE statement ends. If it evaluates to false, or unknown, 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 executed.
else-clause: If none of the conditions specified in the simple-when-clause or searched-when-clause are true, then the statements in the else-clause are executed.
If none of the conditions specified in the WHEN are true, and an ELSE clause is not specified, an error is issued at runtime, and the execution of the CASE statement is terminated (SQLSTATE 20000).
SQL-procedure-statement: Specifies a statement to execute. See SQL-procedure-statement.

Notes

Nesting of CASE statements: CASE statements that use a simple-when-clause can be nested up to three levels. CASE statements that use a searched-when-clause have no limit to the number of nesting levels.

Considerations for SQLSTATE and SQLCODE variables: When the first SQL-procedure-statement in the CASE statement is executed, the SQLSTATE and SQLCODE SQL variables reflect the result of evaluating the expressions or search-conditions of that CASE statement. If a CASE statement does not include an ELSE clause and none of the search-conditions evaluate to true, then any error returned from the expression is returned.

Examples

Example 1: Depending on the value of SQL variable v_workdept, update column DEPTNAME in table DEPARTMENT with the appropriate name.

The following example shows how to do this using the syntax for a simple-when-clause.

               CASE v_workdept
                 WHEN 'A00'
                   THEN UPDATE department SET
                              deptname = 'DATA ACCESS 1';
                 WHEN 'B01'
                   THEN UPDATE department SET
                              deptname = 'DATA ACCESS 2';
                   ELSE UPDATE department SET
                              deptname = 'DATA ACCESS 3';
               END CASE

Example 2: The following example shows how to do this using the syntax for a searched-when-clause:

               CASE
                 WHEN v_workdept = 'A00'
                   THEN UPDATE department SET
                              deptname = 'DATA ACCESS 1';
                 WHEN v_workdept = 'B01'
                   THEN UPDATE department SET
                              deptname = 'DATA ACCESS 2';
                   ELSE UPDATE department SET
                              deptname = 'DATA ACCESS 3';
               END CASE