Mega Code Archive
CASE Statement with Condition Checks
<![CDATA[
The CASE control can also operate without an initial case value, evaluating a condition on each WHEN block.
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)
]]>