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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

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

Notify of

Sort by:   newest | oldest | most voted
Rasheeda Cogbill
Rasheeda Cogbill
7 months 5 days ago

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

3 months 17 days ago

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.