CASE Statement with Condition Checks : CASE : Procedure Function MySQL TUTORIALS


MySQL TUTORIALS » Procedure Function » CASE »

 

CASE Statement with Condition Checks


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(2DEFAULT 0;
    ->
    -> CASE
    -> WHEN preferred = THEN
    ->         SET shipping_cost = 2;
    -> WHEN delivery_day = THEN
    ->         SET shipping_cost = 20;
    -> WHEN delivery_day = THEN
    ->         SET shipping_cost = 15;
    -> WHEN delivery_day = THEN
    ->         SET shipping_cost = 10;
    -> ELSE
    ->         SET shipping_cost = 5;
    -> END CASE;
    -> RETURN shipping_cost;
    ->
    -> END
    -> //
Query OK, rows affected (0.01 sec)

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

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

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

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



Leave a Comment / Note


 
Verification is used to prevent unwanted posts (spam). .

Follow Navioo On Twitter

MySQL TUTORIALS

 Navioo Procedure Function
» CASE