CASE Statement in MySQL

 CASE Statement in MySQL


Both IF and CASE statements allow executing a block of code based on a specific condition.

  • A simple CASE statement is more readable and efficient than an IF statement when comparing a single expression against a range of unique values.
  • When checking complex expressions based on multiple values, the IF statement is easier to understand.
  • If use the CASE statement, have to make sure that at least one of the CASE conditions is matched. Otherwise, need to define an error handler to catch the error. (not do this with the IF statement).
  • In some situations, use both IF and CASE to make the code more readable and efficient.

·         Use for complex conditional statements inside stored procedures.

·         An alternative conditional statement.

·         Make the code more readable and efficient.

·         The CASE statement has two forms: simple CASE and searched CASE statements.


CASE Statement 

Simple CASE statement


CASE case_value

   WHEN when_value1 THEN statements

   WHEN when_value2 THEN statements


   [ELSE else-statements]


·         Simple CASE statement sequentially compares the case_value with the when_value1when_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 clause is available.

·         When the ELSE 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”.



That the case_value can be an exact 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, use an empty BEGIN END block in the ELSE clause:

CASE case_value

    WHEN when_value1 THEN ...

    WHEN when_value2 THEN ...





·         The simple CASE statement tests for equality ( =), not use it with NULL (NULL = NULL returns FALSE).




 CREATE PROCEDURE GetCustomerShipping( IN  pCustomerNUmber INT,  OUT pShipping  VARCHAR(50) )


    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';


                                  SET pShipping = '5-day Shipping';

               END CASE;





·         The getcustomershipping() stored procedure accepts two parameters: pcustomernumber as an IN parameter and pshipping as an OUT parameter.

·         In this 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:

Calls the stored procedure and passes the customer number 112:
CALL GetCustomerShipping(112,@shipping);
the statement returns the shipping time of the customer 112:
SELECT @shipping;
| @shipping      |
| 2-day Shipping |
1 row in set (0.00 sec)

Searched CASE statement

·         The simple CASE the statement only allows comparing a value with a set of distinct values.

·         If To perform more complex matches such as ranges, then use the searched CASE statement.

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

    WHEN search_condition1 THEN statements
    WHEN search_condition1 THEN statements
    [ELSE else-statements]
In this syntax: -
·         Searched CASE evaluates each search_condition in the WHEN clause until it finds 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.
·         Similar to the simple CASE statement, if don’t specify an ELSE clause and no condition is TRUE, then MySQL raises the same error:
·         Case not found for CASE statement 
·         Mysql does not allow to have empty statements in the THEN or ELSE clause. For preventing MySQL raising error, use an empty BEGIN END block in the ELSE clause.
Use a searched case statement to find customer level silver, gold, or platinum-based on the customer’s credit limit.
CREATE PROCEDURE GetDeliveryStatus(IN pOrderNumber INT, 
OUT pDeliveryStatus VARCHAR(100) )
               DECLARE waitingDay INT DEFAULT 0;
    SELECT DATEDIFF(requiredDate, shippedDate) INTO waitingDay FROM orders WHERE orderNumber = pOrderNumber;
                               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';
                                              SET pDeliveryStatus = 'No Information';
               END CASE;          

Explanations :-

·         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 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.


Uses the stored procedure GetDeliveryStatus() to get the delivery status of the order 10100 :

CALL GetDeliveryStatus(10100,@delivery);



Post a Comment