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

Besides the IF 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 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 statement described in this tutorial.

Simple CASE statement

The following is the basic syntax of the simple CASE statement:

Code language: SQL (Structured Query Language) (sql)

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

Code language: PHP (php)

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:

Code language: SQL (Structured Query Language) (sql)

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:

Code language: PHP (php)

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:

Code language: SQL (Structured Query Language) (sql)

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

Code language: SQL (Structured Query Language) (sql)

Here is the output:

Code language: SQL (Structured Query Language) (sql)

Searched CASE statement

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

Here is the basic syntax of the searched CASE statement:

Code language: SQL (Structured Query Language) (sql)

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

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

Code language: PHP (php)

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 searched CASE statement to find customer level SILVER , GOLD or PLATINUM based on customer’s credit limit.

Code language: SQL (Structured Query Language) (sql)

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 :

Code language: SQL (Structured Query Language) (sql)

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 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 statement is easier to understand.
• If you use the CASE 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.