Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
11K+ Views
