CASE WHEN with ELSE : CASE « Procedure Function « MySQL Tutorial






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>








11.15.CASE
11.15.1.For checking a uniform condition you may use the CASE construct
11.15.2.CASE Statement with Condition Checks
11.15.3.Using CASE WHEN statement in a procedure
11.15.4.Using CASE WHEN condition statement in a procedure
11.15.5.CASE WHEN with ELSE