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 ;