How to use value from row derived column from a table in another query?

SELECT...INTO can be used to store the single row column result value from a query to be used in another query depending on that value.

"accounts" table

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| account_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| account_name | varchar(50) | NO   |     | NULL    |                |
| balance      | float       | NO   |     | NULL    |                |
| state        | varchar(50) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Sample data

+------------+--------------+---------+-------+
| account_id | account_name | balance | state |
+------------+--------------+---------+-------+
|          1 | Rico         |     500 | IL    |
|          2 | Mark         |     100 | NY    |
|          3 | Rita         |      50 | NY    |
+------------+--------------+---------+-------+

Lets say we want to get average balance of all accounts in the state of NY from the above table and then use that value in another query.

mysql> SELECT AVG(balance) INTO @avg_bal
    -> FROM accounts
    -> WHERE state = 'NY'
    -> GROUP BY state;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('Average balance in NY is ', @avg_bal) AS result;
+-----------------------------+
| result                      |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)

The above use case arises when we have 2 or more queries depending on each others' output inside a stored procedure.

Below is how the stored procedure would look:

DELIMITER $$

DROP PROCEDURE IF EXISTS get_ny_avg_bal$$

CREATE PROCEDURE get_ny_avg_bal()
BEGIN
    DECLARE avg_bal FLOAT;

    SELECT AVG(balance) INTO avg_bal
    FROM accounts
    WHERE state = 'NY'
    GROUP BY state;

    SELECT CONCAT('Average balance in NY is ', avg_bal) AS result;

END$$

DELIMITER ;

mysql> CALL get_ny_avg_bal();
+-----------------------------+
| result                      |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Use of loops in MySQL stored procedures

Loops -> Repetitive tasks

3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT  and UNTIL clauses that runs until a condition is true

Termination of loop -> LEAVE statement.

I will demonstrate the use of loops the simple loop way.

Simple counter from 1 to 10:

Store below stored procedure in a file named my_loop.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS my_loop$$

CREATE PROCEDURE my_loop()
BEGIN
    DECLARE counter INT DEFAULT 0;

    my_loop: LOOP
        SET counter = counter + 1;

        IF counter = 10 THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    SELECT CONCAT('Done counting up to ', counter) AS result;

END$$

DELIMITER ;

Execute the stored procedure:

mysql> SOURCE my_loop.sql

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL my_loop();

+------------------------+

| result                 |

+------------------------+

| Done counting up to 10 |

+------------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Explaining simply, we are defining a counter that starts with 0 and a LOOP labelled my_loop that increments from 0 onward and a condition inside to check for counter value of 10, when the loop terminates through the use of LEAVE clause. You can give a try to the other loop clauses :)

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.

How to use and get output arguments in stored procedure using OUT parameter mode?

It is sometimes required to get the result of a stored procedure in a variable which can be used later or to output the result.

To do so, we can use the "OUT" parameter mode while defining stored procedures.

In the below section, we will be writing a stored procedure to get the square root of a number returned in an output variable provided by us.

Stored Procedure Definition:

Store the below stored procedure in a file named my_sqrt.sql and save it.

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE my_sqrt(inp_number INT, OUT op_number FLOAT)
BEGIN
    SET op_number=SQRT(inp_number);
END$$

DELIMITER ;

Execute Stored Procedure:

Open your mysql client in terminal and run the above procedure using the below command:

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

Query OK, 0 rows affected (0.00 sec)

Then call the stored procedure to get square root of 100 in an output variable called @sqrt:

Get square root of 100:

mysql> CALL my_sqrt(100, @sqrt);
Query OK, 0 rows affected (0.00 sec)

Now the output of the sqrt operation is stored in @sqrt variable which can be used by us as we desire.

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

That's all it takes :)