CASE
Syntax
Or:
CASE WHENsearch_condition
THENstatement_list
[WHENsearch_condition
THENstatement_list
] ... [ELSEstatement_list
] END CASE
The CASE
statement for stored programs implements a complex conditional construct.Note
There is also a CASE
expression, which differs from the CASE
statement described here. See , "Control Flow Functions". The CASE
statement cannot have an ELSE NULL
clause, and it is terminated with END CASE
instead of END
.
For the first syntax, case_value
is an expression. This value is compared to the when_value
expression in each WHEN
clause until one of them is equal. When an equal when_value
is found, the corresponding THEN
clause statement_list
executes. If no when_value
is equal, the ELSE
clause statement_list
executes, if there is one.
This syntax cannot be used to test for equality with NULL
because NULL = NULL
is false. See , "Working with NULL
Values".
For the second syntax, each WHEN
clause search_condition
expression is evaluated until one is true, at which point its corresponding THEN
clause statement_list
executes. If no search_condition
is equal, the ELSE
clause statement_list
executes, if there is one.
If no when_value
or search_condition
matches the value tested and the CASE
statement contains no ELSE
clause, a Case not found for CASE statement error results.
Each statement_list
consists of one or more SQL statements; an empty statement_list
is not permitted.
To handle situations where no value is matched by any WHEN
clause, use an ELSE
containing an empty BEGIN ... END
block, as shown in this example. (The indentation used here in the ELSE
clause is for purposes of clarity only, and is not otherwise significant.)