In this form of the conditional statement, you can execute a list of SQL statements, including control statements, associated with at most one WHEN clause or ELSE clause, depending on whether operand_1 (value-expression) equals operand_2 (value-expression).
The WHEN clauses are evaluated in the order in which they are specified in the CASE statement. The process of evaluation is as follows:
- The first WHEN clause is evaluated.
- If the value-expression (operand_1)specified in the CASE clause is equal to the value-expression (operand_2)in the WHEN clause, the statements of that WHEN clause are executed.
- Control goes to the next statement in the stored procedure.If the value expressions are not equal, then the next WHEN clause, if it exists, is evaluated.
- All subsequent WHEN clauses are evaluated as described in stage 1.
- When there are no more WHEN clauses to evaluate, the ELSE clause, if it exists, is taken up and the statements of the ELSE clause are executed. Control goes to the next statement in the stored procedure.
- If there is no ELSE clause and the value-expression in the CASE clause does not find a match in any of the WHEN clauses,
- A runtime exception (“Case not found for CASE statement”, SQLSTATE=’20000’, SQLCODE = 7601) occurs.
- The execution of the CASE statement is terminated.