CASE

You can use the CASE statement to build a complex conditional statement.

The CASE statement supports two syntaxes:

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 contain the ELSE NULL clause, and must end with END CASE.

For the first syntax, case_value is an expression. It is compared with the when_value expression in each WHEN clause to find an equal value. When a value equal to when_value is found, the statement_list statement in the THEN clause is executed. If no value is equal to when_value, the statement_list statement (if any) in the ELSE clause is executed. You cannot use this syntax to test the equality with NULL. This is because NULL=NULL means False.

For the second syntax, the search_condition expression of each WHEN clause is evaluated until the result of an expression is True. Then, the statement_list statement in the THEN clause is executed. If no search_condition expression has the value True, the statement_list statement (if any) in the ELSE clause is executed.

If no value is equal to when_value or no search_condition expression has the value True, and the CASE statement does not contain the ELSE clause, the error "Case not found for CASE statement" is returned.

Each statement_list consists of one or more SQL statements and must not be empty.

If no value matches the WHEN clause, use an ELSE clause with an empty BEGIN ... END block, as shown in the following example:

obclient> DELIMITER //

obclient> 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