SQL/MySQL/Procedure Function/CASE

CASE Statement with Condition Checks

   <source lang="sql">

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)

       </source>
   
  

CASE WHEN with ELSE

   <source lang="sql">

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>

       </source>
   
  

For checking a uniform condition you may use the CASE construct

   <source lang="sql">

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>

       </source>
   
  

Using CASE WHEN condition statement in a procedure

   <source lang="sql">

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>

       </source>
   
  

Using CASE WHEN statement in a procedure

   <source lang="sql">

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)

       </source>