MySQL: Procedure Variable vs Session specific User Defined Variable (@variable vs variable)

I have found lots of question and discussion 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 can not access this variable and once session terminates, it destroyed 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 have also one local variable, which we can define and use for specific stored objects like stored procedure.
The scope of this local variable is for that object execution purpose. Once a stored procedure executes, it destroyed automatically.

Below is a small demonstration on use of @variable vs variable:
Create one sample stored procedure with session and local variable:

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.

Anvesh Patel

Leave a Reply

4 Comments on "MySQL: Procedure Variable vs Session specific User Defined Variable (@variable vs variable)"

Notify of
Sort by:   newest | oldest | most voted
Rasheeda Cogbill

Hello. magnificent job. I did not expect this. This is a great story. Thanks!


Very helpful article !

Hello Anvesh !, I came to know that, you are also doing full time job as DB Engineer. I am really very impressed about your contributions, dedication, hardworking and positive attitude towards the learning and sharing.

Man god bless you !
I am going to share your blog in my company. I am working in wipro at hyderabad.


good anvesh!


thank you sir!