Using conditional expressions inside MySQL Stored Procedure

Sometimes there is need to use conditional expression inside stored procedure to control the flow of execution.
We can use IF or CASE statements for this.

Below is a stored procedure to check the performance of a student based on its score.
Store the below stored procedure in a file called get_performance.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS get_performance$$

CREATE PROCEDURE get_performance
    (score NUMERIC(8, 2),
    OUT result VARCHAR(11))

BEGIN
    IF (score >= 90) THEN
        SET result = 'OUTSTANDING';
    ELSEIF (score >= 70 AND score < 90) THEN
        SET result = 'AWESOME';
    ELSEIF (score >= 60 AND score < 70) THEN
        SET result = 'GOOD';
    ELSEIF (score >= 40 AND score < 60) THEN
    SET result = 'OK';
    ELSE
    SET result = 'FAIL';
    END IF;

END$$

DELIMITER ;

Execute the procedure:

mysql> SOURCE get_performance.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Execute the call:

mysql> CALL get_performance(67, @result);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @result;
+---------+
| @result |
+---------+
| GOOD    |
+---------+
1 row in set (0.00 sec)

Basically IF, ELSEIF check for conditions, ELSE matches any condition which has not been matched by any preceding condition and finally THEN executes the set of statements for the satisfied condition and breaks out of the conditional expression. Similarly, we can use CASE for switching to statement sections based on equality comparison with a set of unique values.