MySQL stored procedure return value?

To return a value from stored procedure, you need to use user defined session specific variable. Add @ symbol before variable name.

For example, use @symbol for variable valido. The syntax for the same is as follows:

SELECT @valido;

Whenever you use select statement, you need to use @anyVariableName. The syntax is as follows:

SELECT @anyVariableName;

Here is the demo of stored procedure of return value. The query to create a stored procedure is as follows:

mysql> create procedure ReturnValueFrom_StoredProcedure -> ( -> In num1 int, -> In num2 int, -> out valido int -> ) -> Begin -> IF (num1> 4 and num2 > 5) THEN -> SET valido = (num1+num2); -> ELSE -> SET valido = (num1-num2); -> END IF; -> select @valido; -> end // Query OK, 0 rows affected (0.32 sec) mysql> DELIMITER ;

Call the stored procedure using CALL command. The query is as follows:

mysql> call ReturnValueFrom_StoredProcedure(10,6,@TotalSum); +---------+ | @valido | +---------+ | NULL | +---------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

Now check the result of stored procedure using select statement. The query is as follows:

mysql> select @TotalSum;

The following is the output:

+-----------+ | @TotalSum | +-----------+ | 16 | +-----------+ 1 row in set (0.00 sec)

Now second call for difference of two values. Call the stored procedure.

mysql> call ReturnValueFrom_StoredProcedure(4,2,@TotalDiff); +---------+ | @valido | +---------+ | NULL | +---------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

Check the result from user defined session-specific variable using select statement. The query is as follows:

mysql> select @TotalDiff;

The following is the output:

+------------+ | @TotalDiff | +------------+ | 2 | +------------+ 1 row in set (0.00 sec)