MySQL stored-procedure: out parameter?

MySQLMySQLi Database

Here is a stored procedure that takes one parameter for input (IN) and second parameter for output (OUT)

mysql> delimiter //
mysql> create procedure Sp_SQRT(IN Number1 INT, OUT Number2 FLOAT)
   -> Begin
   -> set Number2=sqrt(Number1);
   -> end;
   -> //
Query OK, 0 rows affected (0.24 sec)
mysql> delimiter ;

Call the stored procedure and send the value to the user variable. The syntax is as follows

CALL yourStoredProcedureName(anyIntegerValue,@anyVariableName);

Check what value is stored in the variable @anyVariableName. The syntax is as follows

SELECT @anyVariableName;

Created the stored procedure with the name ‘Sp_SQRT’. The query is as follows to call the stored procedure

mysql> call Sp_SQRT(36,@MySquareRootNumber);
Query OK, 0 rows affected (0.02 sec)

Check the value of variable @MySquareRootNumber using select statement

mysql> select @MySquareRootNumber;

The following is the output

+---------------------+
| @MySquareRootNumber |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)
raja
Published on 16-Jan-2019 16:41:54
Advertisements