CASE Statement
This MariaDB tutorial explains how to use the CASE statement in MariaDB with syntax and examples.
Description
In MariaDB, the CASE statement has the functionality of an IF-THEN-ELSE statement and has 2 syntaxes that we will explore.
Syntax
The syntax for the CASE statement in MariaDB is:
CASE expression
WHEN value_1 THEN
{...statements to execute when expression equals value_1...}
[ WHEN value_2 THEN
{...statements to execute when expression equals value_2...} ]
[ WHEN value_n THEN result_n
{...statements to execute when expression equals value_n...} ]
[ ELSE
{...statements to execute when no values matched...} ]
END CASE;
OR
CASE
WHEN condition_1 THEN
{...statements to execute when condition_1 is TRUE...}
[ WHEN condition_2 THEN
{...statements to execute when condition_2 is TRUE...} ]
[ WHEN condition_n THEN
{...statements to execute when condition_n is TRUE...} ]
[ ELSE
{...statements to execute when all conditions were FALSE...} ]
END CASE;
Parameters or Arguments
expression
The value that you are comparing to the list of values. (ie: value_1, value_2, ... value_n).
value_1, value_2, ... value_n
These are evaluated in the order listed. Once a value matches expression, the CASE statement will execute the corresponding statements and not evaluate any further.
condition_1, condition_2... condition_n
These conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will execute the corresponding statements and not evaluate the conditions any further.
Note
- The CASE statement will execute the ELSE clause if none of the WHEN clauses were executed.
Example
Let's look at an example that shows how to use the CASE statement in MariaDB. First, we'll look at the first syntax for the CASE statement. For example:
DELIMITER //
CREATE FUNCTION CalcLevel ( value INT )
RETURNS varchar(20) DETERMINISTIC
BEGIN
DECLARE level varchar(20);
CASE value
WHEN 4000 THEN
SET level = 'Low';
WHEN 5000 THEN
SET level = 'Medium';
ELSE
SET level = 'Other';
END CASE;
RETURN level;
END; //
DELIMITER ;
In this CASE statement example, we have used the first syntax for the CASE statement. In this example, if value is equal to 4000, then level will be set to 'Low'. If value is equal to 5000, then level will be set to 'Medium'. Otherwise, level will be set to 'Other'.
Let's look at how to use the CASE statement with the second syntax.
DELIMITER //
CREATE FUNCTION CalcLevel ( value INT )
RETURNS varchar(20) DETERMINISTIC
BEGIN
DECLARE level varchar(20);
CASE
WHEN value <= 4000 THEN
SET level = 'Low';
WHEN value > 4000 AND value <= 5000 THEN
SET level = 'Medium';
ELSE
SET level = 'Other';
END CASE;
RETURN level;
END; //
DELIMITER ;
In this CASE example, we are using the second syntax for the CASE statement. In this example, if value is less than or equal to 4000, then level will be set to 'Low'. If value is greater than 4000 and less than or equal to 5000, then level will be set to 'Medium'. Otherwise, level will be set to 'Other'.