MySQL Stored Procedure Programming

Conditional Control

Conditional control—or “flow of control”—statements allow you to execute code based on the value of some expression. As we said earlier, an expression can be any combination of MySQL literals, variables, operators, and functions that returns a value. Conditional control statements allow you to take different actions depending on the value of such an expression, which could refer to parameters to the stored program, to data in the database, or to other variable data (such as the day of the week or the time of the day).

The MySQL stored program language supports two conditional control statements : IF and CASE. Both IF and CASE perform very similar functions, and there is always a way to rewrite an IF statement as a CASE statement or vice versa. Usually, choosing between IF and CASE is a matter of personal preference or programming standards. However, there are circumstances in which one type of statement is more readable or efficient than the other.

The following subsections describe the syntax of both statements, provide usage examples, and, finally, compare the pros and cons of each.

The IF Statement

All programmers will be familiar with some variation of the IF statement, and MySQL’s implementation of the IF statement contains no surprises. The syntax of IF in stored programs is:

    IF expression THEN commands
        [ELSEIF expression THEN commands ....]
        [ELSE commands]
    END IF;

TRUE or FALSE (or neither)?

The commands associated with IF or ELSEIF statements will only be executed if the associated expression evaluates to TRUE. Expressions such as 1=1 or 2>1 will evaluate to TRUE. Expressions such as 1>3 will evaluate to FALSE.

However, if you are performing an operation on one or more variables, and one of the variables has a NULL value, then the result of the expression can be NULL—neither TRUE nor FALSE. This can lead to some erroneous conclusions if your code assumes that expressions that are not TRUE are necessarily FALSE, or vice versa. So, for instance, in Example 4-5, if we can’t find 'alpha' or 'beta' in the version string, we assume that the release is production. However, if l_version is NULL, then the ELSE condition will always fire, although we actually have no basis for making any such assertion.

Example 4-5. Incorrectly assuming that NOT TRUE = FALSE

   IF (INSTR(l_version_string,'alpha')>0) THEN
         SELECT 'Alpha release of MySQL';
    ELSEIF (INSTR(l_version_string,'beta')>0) THEN
         SELECT 'Beta release of MySQL';
    ELSE
         SELECT 'Production release of MySQL';
    END IF;

Tip

Don’t assume that the result of an expression is either TRUE or FALSE. It could also evaluate to NULL (UNKNOWN) if any of the participating variables is NULL.

Also note that any expressions that return numeric values—or strings that look like numbers—may evaluate to TRUE, FALSE, or NULL. The rules are:

If the absolute value of a numeric expression is 1 or greater, then it will be evaluated to TRUE by the IF or ELSEIF statement. Note that the term “absolute value” means that both 1 and -1 will evaluate to TRUE.
If the value of the numeric expression is 0, then it will evaluate to FALSE.

Simple IF-THEN combinations

In its simplest form, IF can be used to specify a set of statements that executes only if a condition evaluates to TRUE. The syntax for this type of IF statement is as follows:

IF expression THEN
    statements
END IF;

Boolean expressions can return three possible results. When all values in a Boolean expression are known, the result is either TRUE or FALSE. For example, there is no doubt when determining the truth or falsity of an expression such as:

    (2 < 3) AND (5 < 10)

Sometimes, however, you don’t know all values in an expression. That’s because databases allow for values to be NULL, or missing. What, then, can be the result from an expression involving NULLs? For example:

    2 < NULL

Because you don’t know what the missing value is, the only answer you can give is “I don’t know.” This is the essence of so-called three-valued logic, that you can have not only TRUE and FALSE as a possible result, but also NULL.

To learn more about three-valued logic, we recommend C. J. Date’s book Database In Depth: Relational Theory for the Practitioner (O’Reilly).

Example 4-6 shows a simple IF statement.

Example 4-6. Example of simple IF statement

IF sale_value > 200 THEN 

    CALL apply_free_shipping(sale_id);
END IF 
;

We can include multiple statements between the THEN and END IF clauses, as in Example 4-7.

Example 4-7. Multistatement IF statement

IF sale_value > 200 THEN
    CALL apply_free_shipping(sale_id);
    CALL apply_discount(sale_id,10);
END IF;

As shown in Example 4-8, we can also include any other executable statement inside the IF statement, such as looping constructs, SET statements, and other IF statements (although, as we will see later, it’s often best to avoid nesting IF statements in this manner if possible).

Example 4-8. Nested IF statements

IF sale_value > 200 THEN
    CALL apply_free_shipping(sale_id);
    IF sale_value > 500 THEN
        CALL apply_discount(sale_id,20);
    END IF;
END IF;

It is not necessary to break the IF statement across multiple lines; all of the IF statements in Example 4-9 are treated identically by MySQL.

Example 4-9. Alternate formatting for IF statements

IF sale_value > 200 THEN CALL apply_free_shipping(sale_id); END IF;


IF sale_value > 200
THEN
    CALL apply_free_shipping(sale_id);
END IF;


IF sale_value > 200 THEN
    CALL apply_free_shipping(sale_id);
END IF;

It’s probably OK to put a very simple IF statement on a single line, but it is definitely not a good practice to do this for complex or nested IF structures. For instance, which is easier to read, understand, and maintain? This:

    IF sale_value > 200 THEN
        CALL apply_free_shipping(sale_id);
        IF sale_value > 500 THEN
            CALL apply_discount(sale_id,20);
        END IF;
    END IF;

Or this:

    IF sale_value > 200 THEN CALL apply_free_shipping(sale_id); IF sale_value >
    500 THEN CALL apply_discount(sale_id,20);END IF;END IF;

Some programmers like to place the THEN clause on a separate line, as follows:

    IF sale_value > 200
    THEN
        CALL apply_free_shipping(sale_id);
    END IF;

But this is really a matter of personal preference and/or programming standards.

Tip

For any nontrivial IF statement, use indenting and formatting to ensure that the logic of your IF statement is easily understood.

IF-THEN-ELSE statements

Adding an ELSE condition to your IF statements allows you to specify statements that will execute if the IF condition is NOT TRUE. We’ll emphasize again—because it is important—that NOT TRUE does not always mean FALSE. If the IF statement condition evaluates to NULL, then the ELSE statements will still be executed; this can lead to subtle bugs if you don’t protect against NULL variables in your IF conditions.

An IF-THEN-ELSE block has the following syntax:

    IF expression THEN
        statements that execute if the expression is TRUE
    ELSE
        statements that execute if the expression is FALSE or NULL
    END IF;

So in Example 4-10, we apply shipping to an order if it is less than $200; otherwise, we apply a discount (and don’t charge shipping).

Example 4-10. Simple IF-THEN ELSE example

IF sale_value <200 THEN
    CALL apply_shipping(sale_id);
ELSE
    CALL apply_discount(sale_id);
END IF;

IF-THEN-ELSEIF-ELSE statements

The full syntax of the IF statements allows for multiple conditions to be defined. The first condition that evaluates to TRUE will execute. If none of the statements evaluates to TRUE, then the ELSE clause (if present) will execute. The syntax for an IF-THEN-ELSEIF-ELSE IF statement looks like this:

    IF expression THEN
        statements that execute if the expression is TRUE
    ELSEIF expression THEN
        statements that execute if expression1 is TRUE
    ELSE
        statements that execute if all the preceding expressions are FALSE or NULL
    END IF;

You can have as many ELSEIF conditions as you like.

The conditions do not need to be mutually exclusive. That is, more than one of the conditions can evaluate to TRUE. The first condition that evaluates to TRUE is the one that executes. Creating overlapping conditions like this can be useful, but you have to be very careful when ordering the conditions. For instance, consider the IF-ELSEIF statement shown in Example 4-11.

Example 4-11. Example of an IF-ELSEIF block with overlapping conditions

IF (sale_value>200) THEN
    CALL free_shipping(sale_id);
ELSEIF (sale_value >200 and customer_status='PREFERRED') THEN
    CALL free_shipping(sale_id);
    CALL apply_discount(sale_id,20);
END IF;

The intention of this code fragment is clear: apply free shipping to all orders over $200, and add a 20% discount for preferred customers. However, because the first condition will evaluate to TRUE for all orders over $200, the ELSEIF condition will not be evaluated for any orders over $200, and our preferred customers will not get their discount. No discount for preferred customers means no end-of-year bonus for our stored procedure programmer!

There are a number of better ways to craft this statement: for one thing, we could move the ELSEIF condition into the IF clause to ensure that it gets evaluated first; alternately, we could nest an IF statement within the sale_value>200 IF clause to test the customer status, as shown in Example 4-12.

Example 4-12. Two ways of correcting the logic error in the previous example

/* Reordering the IF conditions */
IF (sale_value >200 and customer_status='PREFERED') THEN
        CALL free_shipping(sale_id);
        CALL apply_discount(sale_id,20);
ELSEIF (sale_value>200) THEN
        CALL free_shipping(sale_id);

END IF;


/* Nesting the IF conditions */


IF (sale_value >200) THEN
    CALL free_shipping(sale_id);
    IF (customer_satus='PREFERRED') THEN
       CALL apply_discount(sale_id,20);
    END IF;
END IF:

Both of the alternatives shown in Example 4-12 are perfectly valid. Generally we want to avoid nesting IF statements where possible, but if there are a lot of additional evaluations that we need to conduct when the sale_value is greater than $200, then it might make sense to perform the sale_value test once, and then individually test for all the other conditions. So let’s say our business rules state that for orders over $200 we give free shipping, along with a variable discount based on the customer’s status in our loyalty program. The logic in a single IF-ELSEIF block might look like that shown in Example 4-13.

Example 4-13. IF block with many redundant conditions

IF (sale_value >200 and customer_status='PLATINUM') THEN
    CALL free_shipping(sale_id);      /* Free shipping*/
    CALL apply_discount(sale_id,20);  /* 20% discount */


ELSEIF (sale_value >200 and customer_status='GOLD') THEN
    CALL free_shipping(sale_id);     /* Free shipping*/
    CALL apply_discount(sale_id,15); /* 15% discount */

ELSEIF (sale_value >200 and customer_status='SILVER') THEN
    CALL free_shipping(sale_id);     /* Free shipping*/
    CALL apply_discount(sale_id,10); /* 10% discount */


ELSEIF (sale_value >200 and customer_status='BRONZE') THEN
    CALL free_shipping(sale_id);    /* Free shipping*/
    CALL apply_discount(sale_id,5); /* 5% discount*/

ELSEIF (sale_value>200) THEN
    CALL free_shipping(sale_id);    /* Free shipping*/

END IF;

In this case, the constant repetition of the sale_value condition and the free_shipping call actually undermines the readability of our logic—as well as imposing a performance overhead (see Chapter 22). It might be better to use a nested IF structure that makes it clear that everyone gets free shipping for orders over $200, and that discounts are then applied based on the customer loyalty status only. Example 4-14 shows the nested IF implementation.

Example 4-14. Using nested IF to avoid redundant evaluations

IF (sale_value > 200) THEN
    CALL free_shipping(sale_id);    /*Free shipping*/


    IF (customer_status='PLATINUM') THEN
        CALL apply_discount(sale_id,20); /* 20% discount */


    ELSEIF (customer_status='GOLD') THEN
        CALL apply_discount(sale_id,15); /* 15% discount */


    ELSEIF (customer_status='SILVER') THEN
        CALL apply_discount(sale_id,10); /* 10% discount */


    ELSEIF (customer_status='BRONZE') THEN
        CALL apply_discount(sale_id,5); /* 5% discount*/
    END IF;


END IF;

The CASE Statement

The CASE statement is an alternative conditional execution or flow control statement. Anything that can be done with CASE statements can be done with IF statements (and vice versa), but CASE statements are often more readable and efficient when multiple conditions need to be evaluated, especially when the conditions all compare the output from a single expression.

Simple CASE statement

CASE statements can take two forms. The first—sometimes referred to as a simple CASE statement—compares the output of an expression with multiple conditions:

    CASE expression
        WHEN value THEN
            statements
        [WHEN value THEN
            statements ...]
        [ELSE 

            statements]
    END CASE;

This syntax is useful when we are checking the output of some expression against a set of distinct values. For instance, we could check the customer loyalty status from our previous example using the simple CASE statement shown in Example 4-15.

Example 4-15. Example of a simple CASE statement

CASE customer_status
    WHEN 'PLATINUM'  THEN
        CALL apply_discount(sale_id,20); /* 20% discount */

    WHEN 'GOLD' THEN
        CALL apply_discount(sale_id,15); /* 15% discount */

    WHEN 'SILVER' THEN
        CALL apply_discount(sale_id,10); /* 10% discount */


    WHEN 'BRONZE' THEN
        CALL apply_discount(sale_id,5); /* 5% discount*/
END CASE;

As with the IF command, you can specify multiple WHEN statements and you can specify an ELSE clause that executes if none of the other conditions apply.

However, it is critical to realize that a CASE statement will raise an exception if none of the conditions apply. This means that in Example 4-15 if the customer_status was not one of 'PLATINUM', 'GOLD', 'SILVER', or 'BRONZE' then the following runtime exception would occur:

    ERROR 1339 (20000): Case not found for CASE statement

We could create an exception handler to cause this error to be ignored (as described in Chapter 6), but it is probably better practice to code an ELSE clause to ensure that all possible conditions are handled. So, we should probably adapt the previous example to include an ELSE clause that applies a zero discount to a customer who meets none of the preceding conditions.

Tip

If none of the CASE statements matches the input condition, CASE will raise MySQL error 1339. You should either construct an error handler to ignore this error, or ensure that the exception never occurs by including an ELSE clause in your CASE statement.

The simple CASE statement is useful when comparing the value of an expression to a series of specific values. However, the simple CASE statement cannot easily or naturally match ranges, or handle more complex conditions involving multiple expressions. For these more complex “cases” we can use a “searched” CASE statement, described in the next section.

“Searched” CASE statement

The searched CASE statement is functionally equivalent to an IF-ELSEIF-ELSE-END IF block. The searched CASE statement has the following syntax:

    CASE
        WHEN condition THEN
            statements
        [WHEN condition THEN
            statements...]
        [ELSE
            statements]
    END CASE;

Using the searched CASE structure, we can implement the free shipping and discount logic that we implemented earlier using IF. A direct translation of our sales discount and free shipping logic using a searched CASE statement is shown in Example 4-16.

Example 4-16. Example of a searched CASE statement

CASE
    WHEN  (sale_value >200 AND customer_status='PLATINUM') THEN
        CALL free_shipping(sale_id);     /* Free shipping*/
        CALL apply_discount(sale_id,20); /* 20% discount */


    WHEN  (sale_value >200 AND customer_status='GOLD') THEN
        CALL free_shipping(sale_id);     /* Free shipping*/
        CALL apply_discount(sale_id,15); /* 15% discount */


    WHEN (sale_value >200 AND customer_status='SILVER') THEN
        CALL free_shipping(sale_id);     /* Free shipping*/
        CALL apply_discount(sale_id,10); /* 10% discount */


    WHEN (sale_value >200 AND customer_status='BRONZE') THEN
        CALL free_shipping(sale_id);    /* Free shipping*/
        CALL apply_discount(sale_id,5); /* 5% discount*/

    WHEN (sale_value>200)     THEN
        CALL free_shipping(sale_id);    /* Free shipping*/



END CASE;

However, remember that if none of the WHERE clauses is matched, a 1339 error will occur. Therefore, this code will cause a fatal error if the order is less than $200 or the customer is not in our loyalty program—not a happy outcome. So we should protect our code—and our job security—by including an ELSE clause as shown in Example 4-17.

Example 4-17. Adding a dummy ELSE clause to our searched CASE example

CASE
    WHEN  (sale_value >200 AND customer_status='PLATINUM') THEN
          CALL free_shipping(sale_id);     /* Free shipping*/
          CALL apply_discount(sale_id,20); /* 20% discount */


    WHEN (sale_value >200 AND customer_status='GOLD') THEN
         CALL free_shipping(sale_id);     /* Free shipping*/
         CALL apply_discount(sale_id,15); /* 15% discount */


    WHEN (sale_value >200 AND customer_status='SILVER') THEN
         CALL free_shipping(sale_id);     /* Free shipping*/
         CALL apply_discount(sale_id,10); /* 10% discount */


    WHEN (sale_value >200 AND customer_status='BRONZE') THEN
         CALL free_shipping(sale_id);    /* Free shipping*/
         CALL apply_discount(sale_id,5); /* 5% discount*/

    WHEN (sale_value>200) THEN
         CALL free_shipping(sale_id);    /* Free shipping*/
    ELSE
         SET dummy=dummy;


END CASE;

Note that because MySQL lacks a NULL (do nothing) statement in the stored program language, we had to add a dummy statement—but this statement has negligible overhead .

As with our IF implementation of this logic, we could also use nested CASE statements to perform the same logic with arguably greater clarity. In Example 4-18 we combine simple and searched CASE statements, and also include a “not found” handler to avoid having to include ELSE statements. We enclose the entire thing in a block so that our handler does not inadvertently influence other statements within the stored program.

Example 4-18. Using nested CASE statements and a block-scoped “not found” handler

BEGIN
    DECLARE not_found INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR 1339 SET not_found=1;


    CASE
        WHEN (sale_value>200) THEN
            CALL free_shipping(sale_id);
            CASE customer_status
                WHEN 'PLATINUM' THEN
                    CALL apply_discount(sale_id,20);
                WHEN 'GOLD' THEN
                    CALL apply_discount(sale_id,15);
                WHEN 'SILVER' THEN
                    CALL apply_discount(sale_id,10);
                WHEN 'BRONZE' THEN
                    CALL apply_discount(sale_id,5);
            END CASE;
    END CASE;


END;

IF Versus CASE

We’ve seen that both IF and CASE statements can implement the same flow control functionality. So which is best? To a large extent, choosing between IF and CASE is more a matter of personal preference and programming standards than of any implicit advantages offered by either of the two statements. However, when deciding between CASE and IF, consider the following:

Consistency in style is probably more important than any slight advantages either approach might have in a particular circumstance. We therefore suggest that you choose between CASE and IF consistently, and not randomly switch between the two depending on your mood, the weather, or your horoscope!
CASE is slightly more readable when you are comparing a single expression against a range of distinct values (using a “simple” CASE statement).
IF is probably a more familiar and easily understood construct when you are evaluating ranges or complex expressions based on multiple variables.
If you choose CASE, you need to ensure that at least one of the CASE conditions is matched, or define an error handler to catch the error that will occur if no CASE condition is satisfied. IF has no such restriction.

Remember—whichever construct you use—that:

Once any condition in the CASE or IF structure is satisfied, no more conditions will be evaluated. This means that if your conditions overlap in any way, the order of evaluation is critical.
The MySQL stored program language uses three-valued logic; just because a statement is NOT TRUE does not mean that it is necessary FALSE—it could be NULL.
You should think carefully about the readability of your statements—sometimes a nested set of IF or CASE statements will be more readable and possibly more efficient. However, more often it is better to avoid nesting, especially if the statements become deeply nested (say three or more levels).

Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

MySQL Stored Procedure Programming by Guy Harrison, Steven Feuerstein