Using Conditions

MySQL provides a comprehensive set of conditional statements, allowing you to incorporate complex logic directly into your reports.

IF-THEN-ELSE Statement

The IF statement allows you to execute different blocks of code based on specific conditions.

drop procedure if exists sp_DBR_who_is
$$
create procedure sp_DBR_who_is( 
in_name varchar( 50 ) 
)
begin

declare vtext varchar( 100 );

if (in_name = 'sherlock')  then
    set vtext = concat( "mr. ", in_name, " holmes of course." );
elseif (in_name = 'holmes') then
    set vtext = concat( "mr. sherlock ", in_name, " it is." );
else
    set vtext = concat("who is ", in_name, "? sorry, i don't know this person.");
end if;
select vtext as 'answer';

end
$$

call sp_DBR_who_is( "Sherlock" );
+--------------------------------+
| Answer                         |
+--------------------------------+
| Mr. Sherlock Holmes of course. | 
+--------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

call sp_DBR_who_is( "Holmes" );
+----------------------------+
| Answer                     |
+----------------------------+
| Mr. Sherlock Holmes it is. | 
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

call sp_DBR_who_is( "Watson" );
+-------------------------------------------------+
| Answer                                          |
+-------------------------------------------------+
| Who is Watson? Sorry, I don't know this person. | 
+-------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

CASE Statement

The CASE statement provides an efficient way to handle multiple branching conditions.

drop procedure if exists sp_DBR_number
$$
create procedure sp_DBR_number( in_value int )
begin

case in_value
    when 1 then select 'one';
    when 2 then select 'two';
    else select "sorry, can't do any better";
end case;

end
$$

call sp_DBR_number( 1 );
+-----+
| one |
+-----+
| one | 
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

call sp_DBR_number( 2 );
+-----+
| two |
+-----+
| two | 
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

call sp_DBR_number( 3 );
+----------------------------+
| Sorry, can't do any better |
+----------------------------+
| Sorry, can't do any better | 
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)