Conditional Execution
You can control the flow of execution in your stored
program by using IF or CASE statements. Both have roughly the same
functionality; we will demonstrate the use of IF in this tutorial, as it's probably the
most familiar of the two constructs.
Figure 2-8 shows a stored program that works out the discounted rate for a purchase based on the size of the purchase, and Example 2-5 shows its execution. Purchases over $500 get a 20% discount, while purchases over $100 get a 10% discount.

Figure 2-8. Conditional execution with the IF statement
Example 2-5. Creating and executing a stored procedure that contains an IF statement
mysql>SOURCEdiscounted_price.sql Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) mysql>CALLdiscounted_price(300,@new_price) $$ Query OK, 0 rows affected (0.00 sec) mysql>SELECT@new_price$$ +------------+ | @new_price | +------------+ | 270.0 | +------------+ 1 row in set (0.00 sec)
The IF statement allows you
to test the truth of an expression such as normal_price > 500 and take appropriate
action based on the result of the expression. As with other
programming languages, the ELSEIF
clause is used for all conditional branches after the initial IF. The ELSE clause is executed if the Boolean
expressions in the IF and ELSEIF clauses all evaluate to false.
CASE has very similar functionality, and may be preferable when you ...