Using MySQL CASE Statements in Stored Procedures

Summary: in this tutorial, you will learn how to use MySQL CASE statements to construct complex conditional statements inside stored procedures.

Introduction to MySQL CASE statement

Besides the IF statement, MySQL provides an alternative conditional statement called the CASE statement used in stored procedures. The CASE statements make the code more readable and efficient.

The CASE statement has two forms:

Simple CASE statement
Searched CASE statement.

Note that if you want to add the if-else logic to an SQL statement, you use the CASE expression which differs from the CASE statement covered in this tutorial.

Simple CASE statement

The following is the basic syntax of the simple CASE statement:

CASE case_value
   WHEN when_value1 THEN statements
   WHEN when_value2 THEN statements
   ...
   [ELSE else-statements]
END CASE;Code language: SQL (Structured Query Language) (sql)

In this syntax, the simple CASE statement sequentially compares the case_value is with when_value1, when_value2, and so on until it finds a match.

When the CASE finds a case_value that is equal to a when_value, it executes statements in the corresponding THEN clause.

If the CASE statement cannot find any matches, it executes the else-statements in the ELSE clause if the ELSE clause is available.

If the ELSE clause is not available and the CASE cannot find any matches, it’ll issue the following error:

Case not found for CASE statementCode language: PHP (php)

Notice that the case_value can be a literal value or an expression. The statements can consist of one or more SQL statements and cannot be empty.

To avoid the error when the case_value does not equal any when_value, you can use an empty BEGIN...END block in the ELSE clause:

CASE case_value
    WHEN when_value1 THEN ...
    WHEN when_value2 THEN ...
    ELSE 
        BEGIN
        END;
END CASE;Code language: SQL (Structured Query Language) (sql)

The simple CASE statement tests for equality ( =), therefore, you cannot use it to test equality with NULL because NULL = NULL returns FALSE.

Simple CASE statement example

The following stored procedure illustrates how to use the simple CASE statement:

DELIMITER $$ 

CREATE PROCEDURE GetCustomerShipping(
  IN pCustomerNumber INT, 
  OUT pShipping VARCHAR(50)
) 
BEGIN 
	DECLARE customerCountry VARCHAR(100);
	SELECT 
	  country INTO customerCountry 
	FROM 
	  customers 
	WHERE 
	  customerNumber = pCustomerNumber;

	CASE customerCountry 
		WHEN 'USA' THEN 
			SET pShipping = '2-day Shipping';
		WHEN 'Canada' THEN 
			SET pShipping = '3-day Shipping';
		ELSE 
			SET pShipping = '5-day Shipping';
	END CASE;
END$$ 

DELIMITER ;Code language: PHP (php)

How it works.

The GetCustomerShipping() stored procedure accepts two parameters:

pCustomerNumber as an IN parameter
pShipping as an OUT parameter.

How it works.

First, retrieve the customer’s country by specified customer number from the customers table.

Second, determine the shipping time based on the customer’s country using a simple CASE statement:

If the customer locates in USA , the shipping time is 2-day shipping .
If the customer locates in Canada , the shipping time is 3-day shipping.
The customers from other countries will have to have 5-day shipping .

The following flowchart demonstrates the logic of the CASE statement for determining the shipping time:

This statement calls the stored procedure with the customer id 112 and returns the shipping information:

CALL GetCustomerShipping(112,@shipping);
SELECT @shipping;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+
| @shipping      |
+----------------+
| 2-day Shipping |
+----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Searched CASE statement

The simple CASE statement only allows you to compare a value with a set of distinct values.

To perform more complex matches such as ranges, you use the other form of the CASE statement called the searched CASE statement.

The searched CASE statement is equivalent to the IF statement. However, it’s much more readable than the IF statement.

Here’s the basic syntax of the searched CASE statement:

CASE
    WHEN search_condition1 THEN statements
    WHEN search_condition1 THEN statements
    ...
    [ELSE else-statements]
END CASE;Code language: SQL (Structured Query Language) (sql)

In this syntax, searched CASE evaluates each search_condition in the WHEN clause until it finds a condition that evaluates to TRUE, then it executes the corresponding THEN clause statements.

If no search_condition evaluates to TRUE, the CASE will execute else-statements in the ELSE clause if an ELSE clause is available.

Like a simple CASE statement, if you don’t specify an ELSE clause and no condition is TRUE, MySQL raises the same error:

Case not found for CASE statementCode language: PHP (php)

MySQL also does not allow you to have an empty statements in the THEN or ELSE clause.

If you don’t want to handle the logic in the ELSE clause while preventing MySQL from raising an error in case no search_condition is true, you can use an empty BEGIN...END block in the ELSE clause.

Searched CASE statement example

The following example demonstrates how to use a searched CASE statement to get the delivery status of an order based on the number of waiting days:

DELIMITER $$ 

CREATE PROCEDURE GetDeliveryStatus(
  IN pOrderNumber INT, 
  OUT pDeliveryStatus VARCHAR(100)
) 

BEGIN 
	-- get the waiting day from the orders table
	DECLARE waitingDay INT DEFAULT 0;
	SELECT 
	  DATEDIFF(shippedDate, requiredDate) INTO waitingDay 
	FROM 
	  orders 
	WHERE 
	  orderNumber = pOrderNumber;
      
	-- determine delivery status	  
	CASE 
		WHEN waitingDay < 0 THEN 
			SET pDeliveryStatus = 'Early Delivery';
        WHEN waitingDay = 0 THEN 
			SET pDeliveryStatus = 'On Time';
		WHEN waitingDay >= 1 AND waitingDay < 5 THEN 
			SET pDeliveryStatus = 'Late';
		WHEN waitingDay >= 5 THEN 
			SET pDeliveryStatus = 'Very Late';
		ELSE 
			SET pDeliveryStatus = 'No Information';
	END CASE;

END$$ 

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

How it works.

The stored procedure GetDeliveryStatus() accepts an order number as an IN parameter and returns the delivery status as an OUT parameter.

First, calculate the waiting days between the required date and the shipped date.

Second, determine the delivery status based on the number of waiting days using the searched CASE statement:

If the number of waiting days is negative, then the delivery is early.
If the number of waiting days is zero, then the delivery is on time.
The delivery is late when the number of waiting days is between 1 and 5.
When the number of waiting days is more than 5 days, then the delivery is very late.
If the number of waiting days is NULL or else, the delivery has the status of no information specified in the ELSE clause.

This statement uses the stored procedure GetDeliveryStatus() to get the delivery status of the order 10100 :

CALL GetDeliveryStatus(10100,@delivery);
SELECT @delivery;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+
| @delivery      |
+----------------+
| Early Delivery |
+----------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

MySQL CASE statement vs. IF statement

Both IF and CASE statements allow you to execute a block of code based on a specific condition. Choosing between IF or CASE sometimes is just a matter of personal preference.

Here are some general guidelines:

A simple CASE statement is more readable and efficient than an IF statement when you compare a single expression against a range of unique values. However, when checking complex expressions based on multiple values, the IF statement is easier to understand.
When using the CASE statement, you need to make sure that at least one of the CASE condition is matched. Otherwise, you need to define an error handler to catch the error. Note that you do not have to do this with the IF statement.
In some situations, you can combine both IF and CASE to make the code more readable and efficient.

Summary

Use a simple CASE statement to evaluate a specific expression against a series of possible values and execute corresponding actions.
Use a searched CASE statement to evaluate various conditions individually, allowing for a more flexible code.

Was this tutorial helpful?