totn MariaDB

MariaDB: 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'.

Share on: