CASE Statement with Condition Checks : CASE « Procedure Function « MySQL Tutorial

1. MySQL Tutorial
2. Procedure Function
3. CASE

The CASE control can also operate without an initial case value, evaluating a condition on each WHEN block.

mysql>
mysql>
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction(delivery_day INT(1),preferred INT(1))
    -> RETURNS INT(2)
    -> BEGIN
    ->
    -> DECLARE shipping_cost INT(2) DEFAULT 0;
    ->
    -> CASE
    -> WHEN preferred = 1 THEN
    ->         SET shipping_cost = 2;
    -> WHEN delivery_day = 1 THEN
    ->         SET shipping_cost = 20;
    -> WHEN delivery_day = 2 THEN
    ->         SET shipping_cost = 15;
    -> WHEN delivery_day = 3 THEN
    ->         SET shipping_cost = 10;
    -> ELSE
    ->         SET shipping_cost = 5;
    -> END CASE;
    -> RETURN shipping_cost;
    ->
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql>
mysql> select myFunction(1,1);
+-----------------+
| myFunction(1,1) |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> select myFunction(2,2);
+-----------------+
| myFunction(2,2) |
+-----------------+
|              15 |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> select myFunction(3,3);
+-----------------+
| myFunction(3,3) |
+-----------------+
|              10 |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)