MySQL :: MySQL 8.0 Reference Manual :: 15.6.5.1 CASE Statement (original) (raw)

15.6.5.1 CASE Statement

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

The CASE statement for stored programs implements a complex conditional construct.

For the first syntax, casevalue is an expression. This value is compared to the_whenvalue_ expression in eachWHEN clause until one of them is equal. When an equal whenvalue is found, the corresponding THEN clause_statementlist_ executes. If no_whenvalue_ is equal, theELSE clause_statementlist_ executes, if there is one.

This syntax cannot be used to test for equality withNULL because NULL = NULL is false. See Section 5.3.4.6, “Working with NULL Values”.

For the second syntax, each WHEN clause_searchcondition_ expression is evaluated until one is true, at which point its correspondingTHEN clause_statementlist_ executes. If no_searchcondition_ is equal, theELSE clause_statementlist_ executes, if there is one.

If no whenvalue or_searchcondition_ matches the value tested and the CASE statement contains no ELSE clause, a Case not found for CASE statement error results.

Each statementlist consists of one or more SQL statements; an empty_statementlist_ is not permitted.

To handle situations where no value is matched by anyWHEN clause, use an ELSE containing an emptyBEGIN ... 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.)

DELIMITER |

CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;
  END;
  |