CASE

The CASE statement is used to construct complex conditional statements.

The CASE statement supports two syntax structures, as follows:

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


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

Note that the CASE operator is different from the CASE statement. The CASE statement cannot have an ELSE NULL clause and is terminated with END CASE.

For the first syntax, case_value is an expression that is compared with each when_value expression in the WHEN clauses until a match is found. When a match is found, the corresponding statement_list in the THEN clause is executed. If no match is found, the statement_list in the ELSE clause (if present) is executed. This syntax cannot be used to test for equality with NULL, because NULL = NULL evaluates to False.

For the second syntax, each search_condition expression in the WHEN clauses is evaluated until one evaluates to True. When a True result is found, the corresponding statement_list in the THEN clause is executed. If no search_condition evaluates to True, the statement_list in the ELSE clause (if present) is executed.

If no match is found for when_value or search_condition, and the CASE statement does not contain an ELSE clause, an error "Case not found for CASE statement" is returned.

Each statement_list consists of one or more SQL statements. Empty statement_list is not allowed.

To handle cases where no value matches any WHEN clause, use an ELSE clause that includes an empty BEGIN ... END block, as shown in the following example.

DELIMITER //

CREATE PROCEDURE proc()
   BEGIN
     DECLARE x INT DEFAULT 1;

     CASE x
       WHEN 2 THEN SELECT x;
       WHEN 3 THEN SELECT 0;
       ELSE
         BEGIN
         END;
     END CASE;
   END //
Query OK, 0 rows affected