MySQL CASE Statement

 MySQL CASE Statement

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

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

The CASE the statement has two forms: simpleCASE and searched CASE statements.

Note that if you want to add the if-else logic to an SQL statement, you use the CASE expression which is different from the CASE the statement described 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;

In this syntax, the simple CASE statement sequentially compares the case_value is with the when_value1, when_value2, … until it finds one is equal. When the CASE finds a case_value equal to a when_value, it executes statements in the corresponding THEN clause.

If CASE cannot find any when_value equal to the case_value, it executes the else-statements in the ELSE clause if the ELSE a clause is available.

When the ELSE the clause does not exist and the CASE cannot find any when_value equal to the case_value, it issues an error:

Case not found for CASE statement

Note that the case_value can be a literal value or an expression. The statements can be one or more SQL statements, and cannot have zero statements.

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 as follows:

CASE case_value
    WHEN when_value1 THEN ...
    WHEN when_value2 THEN ...
    ELSE 
        BEGIN
        END;
END CASE;

The simple CASE statement tests for equality ( =), 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 ;

How it works.

The GetCustomerShipping() stored procedure accepts two parameters: pCustomerNumber as an IN parameter and pShipping as an OUT parameter.

In the stored procedure:

First, select the country of the customer from the customers table by the input customer number.

Second, use the simple CASE statement to determine the shipping time based on the country of the customer. 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 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 and passes the customer number 112:

CALL GetCustomerShipping(112,@shipping);

The following statement returns the shipping time of the customer 112:

SELECT @shipping;

Here is the output:

+----------------+
| @shipping      |
+----------------+
| 2-day Shipping |
+----------------+
1 row in set (0.00 sec)

Searched CASE statement

The simple CASE the 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 search CASE statement. The searched CASE the statement is equivalent to the IF  the statement, however, it’s much more readable than the IF statement.

Here is the basic syntax of the searched CASE statement:

CASE
    WHEN search_condition1 THEN statements
    WHEN search_condition1 THEN statements
    ...
    [ELSE else-statements]
END CASE;

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 a clause is available.

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

Case not found for CASE statement

MySQL also does not allow you to have 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 search CASE statement to find customer level SILVER , GOLD or PLATINUM based on the customer’s credit limit.

DELIMITER $$

CREATE PROCEDURE GetDeliveryStatus(
	IN pOrderNumber INT,
    OUT pDeliveryStatus VARCHAR(100)
)
BEGIN
	DECLARE waitingDay INT DEFAULT 0;
    SELECT 
		DATEDIFF(requiredDate, shippedDate)
	INTO waitingDay
	FROM orders
    WHERE orderNumber = pOrderNumber;
    
    CASE 
		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 ;

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 number of 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 zero, then the delivery is on time.
• When the number of waiting days is between 1 and 5, the delivery is late.
• 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);

Here is the result:

MySQL CASE vs. IF

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 guidelines:

• A simple CASE the statement is more readable and efficient than an IF statement when you compare a single expression against a range of unique values.
• When you check complex expressions based on multiple values, the IF the statement is easier to understand.
• If you use the CASE the statement, you have 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 use both IF and CASE to make the code more readable and efficient.

In this tutorial, you have learned how to use two forms of the MySQL CASE statements including simple CASE statement and searched CASE statement.