Background documentationGeneral CASE Statement (searched_case_statement) Locate this document in the navigation structure

 

The general CASE statement (searched_case_statement) is a syntax element that can be used in a routine to define a database procedure (see CREATE DBPROC[EDURE] statement), a database function (see CREATE FUNCTION statement), or a trigger (see CREATE TRIGGER statement).

Structure

Syntax Syntax

  1. <searched_case_statement> ::=
      CASE
        <searched_case_when_clause>...
        [<case_else_clause>]
      END [CASE]
    
    <searched_case_when_clause> ::=
      WHEN <search_condition> THEN <statement>;
    
    <case_else_clause> ::=
      ELSE <statement>;
End of the code.
Explanation

A CASE statement (case_statement) allows the conditional execution of a statement depending on search conditions or equality of operands.

For a general CASE statement (searched_case_statement), the first fulfilled search condition is determined, the associated statement executed, and the CASE statement then ended.

Example Example

CASE

WHEN digit = 0 THEN toCHAR = 'zero';

WHEN digit = 1 THEN toCHAR = 'one';

WHEN digit = 2 THEN toCHAR = 'two';

WHEN digit = 3 THEN toCHAR = 'three';

WHEN digit = 4 THEN toCHAR = 'four';

WHEN digit = 5 THEN toCHAR = 'five';

WHEN digit = 6 THEN toCHAR = 'six';

WHEN digit = 7 THEN toCHAR = 'seven';

WHEN digit = 8 THEN toCHAR = 'eight';

WHEN digit = 9 THEN toCHAR = 'nine';

ELSE STOP(-29000, 'no digit');

END CASE

End of the example.

If no matching literal or fulfilled search condition exists in a CASE statement, the statement defined in the ELSE branch is executed.

If there is no ELSE branch, the runtime error -28901 is returned.