This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I found lots of questions and discussions on the difference between stored procedure local variable and session-specific user-defined variable of MySQL.
MySQL provides session specific user defined variable to initialize and use during the session.
The scope of this variable is for the specific session only and outside of that session another client connection cannot access this variable and once session terminates, it destroys automatically.
This is loosely typed variables and we can initialize somewhere in a session.
We can write @var_name to define a session specific variable.
We also have one local variable, which we can define and use for specific stored objects like a stored procedure.
The scope of this local variable is for that object execution purpose. Once a stored procedure executes, it destroys automatically.
Below is a small demonstration on use of @variable vs variable:
Create a sample stored procedure with session and local variable:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DELIMITER // CREATE PROCEDURE usp_TestVariables() BEGIN -- Declare Local Variable..... DECLARE MyVariable INT DEFAULT 1; SET MyVariable := MyVariable + 1; -- Define Session Specific Variable..... SET @MyVariable := @MyVariable + 1; SELECT MyVariable, @MyVariable; END; -- Assign value to user defined variable. SET @MyVariable = 1; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- Execute this stored procedure multiple times to check a value of both the variables. CALL usp_TestVariables(); --The result of first call. MyVariable @MyVariable ----------- ---------------- 2 2 --The result of second call. MyVariable @MyVariable ----------- ---------------- 2 3 --The result of third call. MyVariable @MyVariable ----------- ---------------- 2 4 --The result of fourth call. MyVariable @MyVariable ----------- ---------------- 2 5 |
You can check the above result, where @MyVariable is incrementing because it persists values until session end.
A stored procedure local variable is not incrementing because it destroyed after every execution of the stored procedure.
