In MariaDB, the CASE statement can be used in stored programs to perform a complex conditional construct. It compares a list of conditions and returns a different result depending on which condition (if any) is matched.

The CASE statement is distinct from the CASE operator, in that the CASE statement is specifically for use in stored programs. Also, there’s a slight difference in the syntax.

Syntax

The CASE statement can be used either like this:

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

Or like this:

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

Example

Here’s an example of a basic stored procedure with the first CASE syntax:

DELIMITER |
CREATE PROCEDURE sp_what_to_wear(
    IN weather VARCHAR(10),
    OUT wearThis VARCHAR(50)
)
BEGIN
  CASE weather
    WHEN 'Rain' THEN SET wearThis = 'Raincoat';
    WHEN 'Sunny' THEN SET wearThis = 'Sunglasses';
    ELSE SET wearThis = 'Whatever you want!';
  END CASE;
END;
|

This syntax is referred to as the simple CASE expression. The simple CASE expression compares an expression to a set of simple expressions to determine the result.

Now when we call that procedure, the output depends on our input:

CALL sp_what_to_wear( 'Rain', @clothing );
SELECT @clothing AS Result_1;

CALL sp_what_to_wear( 'Sunny', @clothing );
SELECT @clothing AS Result_2;

CALL sp_what_to_wear( 'Cloudy', @clothing );
SELECT @clothing AS Result_3;

Result:

Result_1
--------
Raincoat

Result_2
----------
Sunglasses

Result_3
------------------
Whatever you want!

See the MariaDB CASE operator for an example of how the second syntax (the searched CASE expression) works.

The CASE Operator

As mentioned, in MariaDB there’s a distinction between the CASE statement and the CASE operator. The CASE statement is specifically for use in stored programs. Also, the CASE statement cannot have an ELSE NULL clause (the CASE operator can), and it is terminated with END CASE instead of END (which is how the CASE operator is terminated).