SQL/MySQL/Procedure Function/CASE

CASE Statement with Condition Checks

 
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)

CASE WHEN with ELSE

 
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(id int)
    -> BEGIN
    ->     CASE
    ->          WHEN id < 2  THEN
    ->               select "less than 2";
    ->          WHEN id > 2 and id < 5 THEN
    ->               select "greater than 2 and less than 5";
    ->          ELSE
    ->               select "ELSE";
    ->     END CASE;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call myProc(1);
+-------------+
| less than 2 |
+-------------+
| less than 2 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> call myProc(3);
+--------------------------------+
| greater than 2 and less than 5 |
+--------------------------------+
| greater than 2 and less than 5 |
+--------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>

For checking a uniform condition you may use the CASE construct

 
mysql>
mysql>
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction (delivery_day INT(1)) RETURNS INT(2)
    -> BEGIN
    ->
    -> DECLARE shipping_cost INT(2) DEFAULT 0;
    ->
    -> CASE delivery_day
    -> WHEN 1 THEN
    ->         SET shipping_cost = 20;
    -> WHEN 2 THEN
    ->         SET shipping_cost = 15;
    -> WHEN 3 THEN
    ->         SET shipping_cost = 10;
    -> ELSE
    ->         SET shipping_cost = 5;
    -> END CASE;
    -> RETURN shipping_cost;
    ->
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(1);
+---------------+
| myFunction(1) |
+---------------+
|            20 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> select myFunction(2);
+---------------+
| myFunction(2) |
+---------------+
|            15 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> select myFunction(3);
+---------------+
| myFunction(3) |
+---------------+
|            10 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

Using CASE WHEN condition statement in a procedure

 
mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc(id int)
    -> BEGIN
    ->
    ->     CASE
    ->          WHEN id < 2  THEN
    ->               select "less than 2";
    ->
    ->          WHEN id > 2 and id < 5 THEN
    ->               select "greater than 2 and less than 5";
    ->
    ->          WHEN id > 5 THEN
    ->               select "greater than 5";
    ->
    ->     END CASE;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc(2);
ERROR 1339 (20000): Case not found for CASE statement
mysql> call myProc(20);
+----------------+
| greater than 5 |
+----------------+
| greater than 5 |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>

Using CASE WHEN statement in a procedure

 
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(customer_status VARCHAR(10))
    -> BEGIN
    ->
    ->     CASE customer_status
    ->          WHEN "PLATINUM"  THEN
    ->               select "PLATINUM";
    ->
    ->          WHEN "GOLD" THEN
    ->               select "GOLD";
    ->
    ->          WHEN "SILVER" THEN
    ->               select "SILVER";
    ->
    ->          WHEN "BRONZE" THEN
    ->               select "BRONZE";
    ->     END CASE;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc("BRONZE");
+--------+
| BRONZE |
+--------+
| BRONZE |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call myProc("GOLD");
+------+
| GOLD |
+------+
| GOLD |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)