Variables Scope in SQL Procedure

Variable Scope in SQL Procedure is used to define the scope of a variable used in SQL.

Understand with Example

The Tutorial illustrates an example from Variable Scope in SQL Procedure. 

For sake of your understanding : 

Local  variables - These variable are declared inside a inner block and cannot be referenced by outside blocks.

Global variables - These variable  are declared in a outer block and  reference itself and its inner blocks.

To understand Variable Scope in SQL, we create a Procedure abc that include a Local variable. The Local variable are declared in a inner block and cannot be accessed by outside blocks In this example we declare a variable 'x', whose data type is char and default value is 'inner' in the Local variables. The SQL Query include a outer block and declare a variable 'x' whose data type is 'char' type. These are declared in outer block which can be referenced by its itself and inner blocks.

 Create Procedure

delimiter $$

create procedure abc()
BEGIN
DECLARE x CHAR(5) DEFAULT 'outer';

	BEGIN 
		DECLARE x CHAR(5) DEFAULT 'inner';
		Select x;
	END;
Select x;
END$$

delimiter ;

Call Procedure

To invoke a Procedure 'abc', we use the given below Syntax :

call abc();

Result

+-------+
| x     |
+-------+
| inner |
+-------+
1 row in set (0.00 sec)

+-------+
| x     |
+-------+
| outer |
+-------+
1 row in set (0.03 sec)