How to Use a CASE-WHEN Statement in a MySQL Stored Procedure

In this tutorial, we are going to see how to use the statement case-when in a MySQL stored procedure to build a complex conditional statements. In MySQL, the CASE statement has the same functionality as the IF-THEN-ELSE statement and has two syntaxes.
 

Syntax 1:

In this syntax, CASE matches the value with the values “value1”, “value2”, etc., and returns the corresponding statement. If the value is not equal to any values, CASE returns the statement in the ELSE clause if it is specified.

CASE value
   WHEN value1 THEN instruction1
   WHEN value2 THEN instruction2
   …
   [ELSE instruction3]
END

 
 

Example:

DELIMITER $
 
CREATE PROCEDURE getDeliveryDate(
    IN  customerID INT,
    OUT deliveryDate Date
)
BEGIN
    DECLARE customer_country VARCHAR(20);
 
    SELECT country
    INTO customer_country
    FROM customer
    WHERE id = customerID;
 
    CASE customer_country
        WHEN 'US' THEN
           SET deliveryDate = '01/12/2020';
        WHEN 'UK' THEN
           SET deliveryDate = '15/05/2020';
        ELSE
           SET deliveryDate = '00/00/0000';
    END CASE;
END$
 
DELIMITER ;

 

Syntaxe 2:

In this syntax, CASE evaluates the conditions specified in WHEN clauses. If a condition evaluates to true. CASE returns the corresponding statement in THEN clause. Otherwise, the statement specified in the ELSE clause is returned. If the ELSE clause does not exist, the CASE expression returns NULL.

CASE
   WHEN condition1 THEN instruction1
   WHEN condition2 THEN instruction2
   …
   [ELSE instruction3]
END

 

Example:

DELIMITER $
 
CREATE PROCEDURE getDeliveryDate(
    IN  customerID INT,
    OUT deliveryDate Date
)
BEGIN
    DECLARE nbrOfProduct INT DEFAULT 0;
 
    SELECT COUNT(product)
    INTO nbrOfProduct 
    FROM customer c
    JOIN order o
    ON c.id = o.id;
 
    CASE customer_country
        WHEN nbrOfProduct = 10 THEN
           SET deliveryDate = '01/12/2020';
        WHEN nbrOfProduct > 50 THEN
           SET deliveryDate = '15/05/2020';
        ELSE
           SET deliveryDate = '00/00/0000';
    END CASE;
END$
 
DELIMITER ;

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More