Scope of variable in SQL Server Statements

Written on May 04, 2016

Scopes in SQL is different from languages like c#, c, java etc. If we talk about particular language c#, then each statements in block has different scope. Consider below code.

Here "strName" variable outside if-block is having global scope, and same name variable inside if-block is having it's own scope. So we see different outputs.

Statement blocks inside SQL are defined using BEGIN and END keywords. But here's a twist. Declarations inside BEGIN and END statements inside SQL does not have it's own scope. If we write above C# code in SQL it'll give error, because BEGIN-END statement does not have its own scope and we have already defined variable @strName earlier.


So, how scope works in SQL?

Each procedure/UDF have its own scope. Apart from it, each batch of statements has its own scope. If you are wondering how batch is created then all statements inside query are considered as one batch, unless they are not separated using "GO" keyword. "GO" keyword will end batch.

Let's take same example using GO keyword.

This time it won't give error, because both variables are in now different batches hence in different scopes. And output is like below.


Hiren
Maulik

One interesting feature of GO statement is that, if we want to run specific batch at several times then we can specify no. of times we want to repeat it by adding number after GO.

Here, we wrote "GO 5". So it repeated same batch 5 times.


Hope you get good idea of variable scope in SQL. There is good blog article regarding scopes here.