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 :)