CASE statement

The CASE statement selects an execution path based on multiple conditions. This statement should not be confused with the CASE expression, which allows an expression to be selected based on the evaluation of one or more conditions.

Invocation

This statement can be embedded in:

An SQL procedure definition
A compound SQL (compiled) statement
A compound SQL (inlined) statement

The compound SQL statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. The CASE statement is not an executable statement and cannot be dynamically prepared.

Authorization

No privileges are required to invoke the CASE statement. However, the privileges held by the authorization ID of the statement must include all necessary privileges to invoke the SQL statements and expressions that are embedded in the CASE statement.

Syntax

simple-case-statement-when-clause

searched-case-statement-when-clause

Description

CASE: Begins a case-statement.; The value of the expression before the first WHEN keyword is tested for equality with the value of each expression that follows the WHEN keyword. If the search condition is true, the THEN statement is executed. If the result is unknown or false, processing continues to the next search condition. 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.; The search-condition following the WHEN keyword is evaluated. If it evaluates to true, the statements in the associated THEN clause are processed. 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 processed.; Specifies a statement that should be invoked. See SQL-procedure-statement in Compound SQL (compiled) statement.
END CASE: Ends a case-statement.

Notes

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).
Ensure that your CASE statement covers all possible execution conditions.

Examples

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

Example 1: The following example shows how to do this using the syntax for a simple-case-statement-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-case-statement-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