Create variables in MySQL stored procedure with DECLARE keyword

Use MySQL DECLARE for variables in stored procedure −

DECLARE anyVariableName int DEFAULT anyValue;

Let us implement the above syntax in order to create variables in stored procedure −

mysql> DELIMITER //
mysql> CREATE PROCEDURE variable_Demo()
    -> BEGIN
    -> DECLARE lastInsertedId int DEFAULT -1;
    -> select lastInsertedId;
    -> set @providedLastId=10001;
    -> select @providedLastId;
    -> END
    -> //
Query OK, 0 rows affected (0.32 sec)
mysql> DELIMITER ;

Now you can call the above stored procedure using CALL command −

mysql> call variable_Demo();

This will produce the following output −

+----------------+
| lastInsertedId |
+----------------+
|             -1 |
+----------------+
1 row in set (0.00 sec)

+-----------------+
| @providedLastId |
+-----------------+
|           10001 |
+-----------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.04 sec)

AmitDiwan

Published on 08-Nov-2019 10:48:04

• Related Questions & Answers
• Create a stored procedure with delimiter in MySQL
• How can I create MySQL stored procedure with IN parameter?
• MySQL Stored Procedure to create a table?
• How can user variables be used in MySQL stored procedure?
• How can local variables be used in MySQL stored procedure?
• How can I create MySQL stored procedure with OUT parameter?
• How can I create MySQL stored procedure with INOUT parameter?
• MySQL Stored procedure to declare two values and perform mathematical operation
• MySQL stored procedure to execute SHOW CREATE TABLE?
• How to set two variables in a stored procedure with a single MySQL select statement?
• What is stored procedure and how can we create MySQL stored procedures?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• MySQL stored-procedure: out parameter?
• MySQL stored procedure return value?
• Working with WHERE IN() in a MySQL Stored Procedure