Detailed example of using case statement in MySQL stored procedure

This article uses an example to illustrate the usage of the case statement in MySQL stored procedures. Share with you for your reference, the details are as follows:

In addition to the if statement, MySQL provides an alternative conditional statement CASE. MySQL's CASE statement makes the code more readable and efficient. Without further ado, let's take a look at the syntax of a simple case statement:

CASE case_expression
  WHEN when_expression_1 THEN commands
  WHEN when_expression_2 THEN commands
  ...
  ELSE commands
END CASE;

We can use a simple CASE statement to check whether the value of an expression matches a set of unique values. In the above SQL, case_expression can be any valid expression. We compare the value of case_expression with the when_expression in each WHEN clause, such as when_expression_1, when_expression_2, and so on. If the values ​​of case_expression and when_expression_n are equal, the commands in the corresponding WHEN branch are executed. If the when_expression in the WHEN clause matches the value of the case_expression, the commands in the ELSE clause are executed. The ELSE clause is optional. If we omit the ELSE clause and no match is found, MySQL will raise an error. Let's look at an example using a simple CASE statement:

DELIMITER $$
CREATE PROCEDURE GetCustomerShipping(
 in p_customerNumber int(11), 
 out p_shiping varchar(50))
BEGIN
  DECLARE customerCountry varchar(50);
  SELECT country INTO customerCountry
 FROM customers
 WHERE customerNumber = p_customerNumber;
  CASE customerCountry
 WHEN 'USA' THEN
  SET p_shiping = '2-day Shipping';
 WHEN 'CANADA' THEN
  SET p_shiping = '3-day Shipping';
 ELSE
  SET p_shiping = '5-day Shipping';
 END CASE;
END$$

The above sql works like this:

• The GetCustomerShipping stored procedure accepts a customer number as an IN parameter and returns the shipping time based on the customer's country.
• In the stored procedure, first, we get the customer's country based on the entered customer number. A simple CASE statement is then used to compare the customer's country to determine the shipping period. If the customer is located in the United States (USA), the delivery period is 2 days. If the customer is in Canada, the delivery period is 3 days. Customers from other countries will need 5 days shipping time.

Let’s take a look at the flowchart of the logic for determining the shipping time:

The following is the test script for the above stored procedure:

SET @customerNo = 112;
SELECT country into @country
FROM customers
WHERE customernumber = @customerNo;
CALL GetCustomerShipping(@customerNo,@shipping);
SELECT @customerNo AS Customer,
    @country AS Country,
    @shipping AS Shipping;

Execute the above code and get the following results:

+----------+----------+----------------+
| Customer | Country | Shipping |
+----------+----------+----------------+
| 112 | USA | 2-day Shipping |
+----------+----------+----------------+
1 row in set

The simple CASE statement simply allows us to match the value of an expression against a set of different values. To perform more complex matching, such as ranges, we can use a searchable CASE statement. The searchable CASE statement is equivalent to the IF statement, but its construction is more readable. Let's take a look at its grammatical structure:

CASE
  WHEN condition_1 THEN commands
  WHEN condition_2 THEN commands
  ...
  ELSE commands
END CASE;

The above SQL will first evaluate each condition in the WHEN clause until a condition with a value of TRUE is found, and then execute the corresponding commands in the THEN clause. If none of the conditions are TRUE, the commands in the ELSE clause are executed. If you do not specify an ELSE clause and none of the conditions evaluates to TRUE, MySQL issues an error message. MySQL does not allow an empty command in a THEN or ELSE clause. If we don’t want to process the logic in the ELSE clause and at the same time prevent mysql from throwing an error, we can put an empty BEGIN END block in the ELSE clause. Let's look at an example of using a searchable CASE statement to find customers with the SILVER, GOLD, or PLATINUM credit limit:

DELIMITER $$
CREATE PROCEDURE GetCustomerLevel(
 in p_customerNumber int(11), 
 out p_customerLevel varchar(10))
BEGIN
  DECLARE creditlim double;
  SELECT creditlimit INTO creditlimit
 FROM customers
 WHERE customerNumber = p_customerNumber;
  CASE 
 WHEN creditlim > 50000 THEN 
  SET p_customerLevel = 'PLATINUM';
 WHEN (creditlim <= 50000 AND creditlim >= 10000) THEN
  SET p_customerLevel = 'GOLD';
 WHEN creditlim < 10000 THEN
  SET p_customerLevel = 'SILVER';
 END CASE;
END$$

In the above query logic, if the credit limit is:

• If it is greater than 50K, the customer is a PLATINUM customer.
• If less than 50K and greater than 10K, the customer is a GOLD customer.
• If it is less than 10K, then the customer is a SILVER customer.

We can test the stored procedure by executing the following test script:

CALL GetCustomerLevel(112,@level);
SELECT @level AS 'Customer Level';

Execute the above query statement and get the following results:

+----------------+
| Customer Level |
+----------------+
| PLATINUM |
+----------------+
1 row in set

Okay, that’s all for this sharing.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.