SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › CASE Statement

CASE Statement

The CASE works in the same way an IF statement works to control which SQL statements, based on predicates you specify, are executed.

Following is the syntax for the CASE statement:

►►─ CASE ─┬─ proc-value-expression ─ simple-when-stmt-list ─┬─────────────────►
          └─ searched-when-stmt-list ───────────────────────┘

 ►─┬─────────────────────────────┬─ END CASE ─────────────────────────────────►◄
   └─ ELSE ─ proc-SQL-stmt-list ─┘

Expansion of Where simple-when-stmt-list is defined as

   ┌────────────────────────────────────────────────────────────┐
├──▼─ WHEN ─ proc-value-expression ─ THEN ─ proc-SQL-stmt-list ─┴──────────────┤

Expansion of Where searched-when-stmt-list is defined as

   ┌────────────────────────────────────────────────────────────┐
├──▼─ WHEN ─ proc-search-condition ─ THEN ─ proc-SQL-stmt-list ─┴──────────────┤

Expansion of Where proc-SQL-stmt-list is defined as

   ┌──────────────────────────┐
├──▼─ proc-SQL-stmt ─ ; ──────┴────────────────────────────────────────────────┤

Note: For proc-SQL-stmt syntax, see the syntax fragment in CREATE PROCEDURE Syntax and Description.

proc-value-expression

A proc-value-expression is a variant form of the expressions used to specify values as follows:

• To the syntax for expressions, the proc-value-expression adds SQL variable names (see SQL-variable-declarations in Compound Statement) and SQL parameter names (see SQL-parameter-name in CREATE PROCEDURE Syntax and Description). To view SQL variable names and SQL parameter names in the syntax diagram for expressions, see Expressions.
• A proc-value-expression does not allow the use of column functions.

  Note: Scalar functions, however, are allowed.

• A proc-value-expression does not allow the use of column names.
• A proc-value-expression does not allow the use of host variables.

simple-when-stmt-list

A simple-when-stmt-list is a list of simple-when statements, each terminated by a semicolon. For more information, see the example that follows.

searched-when-stmt-list

A searched-when-stmt-list is a list of searched-when statements, each terminated by a semicolon. For more information, see the example that follows.

proc-search-condition

The proc-search-condition specifies a condition that is true, false, or unknown about a row. The proc-search-condition is similar to the search-condition described in with the following modifications that allow its use in SQL Procedures:

• SQL parameter and SQL variable references are allowed instead of column and host-variable references.
• Aggregate functions and any predicate involving a sub-query are not allowed.

proc-SQL-stmt-list

A proc-SQL-stmt-list is a list of proc-SQL-stmt statements, each terminated by a semicolon.