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.

Use of DECLARE and SET in MySQL stored program

DELIMITER $$                    -- Change the delimiter

DROP PROCEDURE IF EXISTS demo$$ -- Remove if previously exists
CREATE PROCEDURE demo()         -- Create Stored Procedure
BEGIN                           -- Start of definition
    DECLARE my_int INT;         -- Declare variables
    DECLARE my_big_int BIGINT;
    DECLARE my_text TEXT;

    DECLARE my_name VARCHAR(30) -- Declare variables with 
        DEFAULT 'Rookie Dev';   -- default value set

    SET my_int = 20;            -- Set variables to values
    SET my_big_int = POWER(my_int, 50);

END$$                           -- End of definition
DELIMITER ;                     -- Reset the delimiter