How can local variables be used in MySQL stored procedure?

Local variables are those variables that are declared within the stored procedure. They are only valid within the BEGIN…END block where they are declared and can have any SQL data type. To demonstrate it, we are creating the following procedure −

mysql> DELIMITER // ;
mysql> Create Procedure Proc_Localvariables()
   -> BEGIN
   -> DECLARE X INT DEFAULT 100;
   -> DECLARE Y INT;
   -> DECLARE Z INT;
   -> DECLARE A INT;
   -> SET Y = 250;
   -> SET Z = 200;
   -> SET A = X+Y+Z;
   -> SELECT X,Y,Z,A;
   -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> Delimiter ; //
mysql> CALL Proc_Localvariables();
+------+------+------+------+
| X    | Y    | Z    | A    |
+------+------+------+------+
| 100  | 250  | 200  | 550  |
+------+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Varun

Published on 21-Feb-2018 08:08:42

• Related Questions & Answers
• How can user variables be used in MySQL stored procedure?
• How Can MySQL CASE statement be used in stored procedure?
• How MySQL IF statement can be used in a stored procedure?
• How MySQL WHILE loop statement can be used in stored procedure?
• How MySQL REPEAT loop statement can be used in stored procedure?
• How Can MySQL LOOP statement be used in a stored procedure?
• How MySQL IF ELSE statement can be used in a stored procedure?
• How can MySQL IF ELSEIF ELSE statement be used in a stored procedure?
• Can access modifiers be used for local variables in Java?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• How can we invoke MySQL stored procedure?
• Create variables in MySQL stored procedure with DECLARE keyword
• How can we alter a MySQL stored procedure?
• How can we drop a MySQL stored procedure?
• What is stored procedure and how can we create MySQL stored procedures?