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