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
How to set two variables in a stored procedure with a single MySQL select statement?
For this, let us first create a new table in MySQL
mysql> create table useProcedure - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > FirstName varchar(20), - > LastName varchar(20) - > ); Query OK, 0 rows affected (0.65 sec)
Insert some records in the table using insert command.
The query is as follows
mysql> insert into useProcedure(FirstName,LastName) values('Adam','Smith');
Query OK, 1 row affected (0.27 sec)The following is your stored procedure to set two variables in a stored procedure with single select statement. The query to create a stored procedure is as follows
mysql> DELIMITER // mysql> create procedure Sp_Demo() - > BEGIN - > declare one varchar(20); - > declare two varchar(20); - > select FirstName, LastName INTO one, two from useProcedure where id = 1; - > select one, two; - > END; - > // Query OK, 0 rows affected (0.26 sec) mysql> DELIMITER ;
Call the stored procedure with the help of CALL command.
The query is as follows
mysql> call Sp_Demo();
The following is the output
+------+-------+ | one | two | +------+-------+ | Adam | Smith | +------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
Updated on: 30-Jul-2019
2K+ Views
Advertisements