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

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.

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

Then call the stored procedure to get

That's all it takes :)

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

## No comments:

## Post a Comment