Scope of Variable in SQL Server Statements

Scopes in SQL are different from languages like C#, C, Java, etc. If we talk about a particular language, say C#, then each statement in a block has a different scope. Typically, a variable is unrestricted and plays between when announced and the ending of the batch, function, or procedure that it is defined in. It is clear, but it is uncomplicated to overlook what comprises a batch.

The scope of a variable is the coverage of any SQL statements that can reach the variable. This scope is defined from the point of declaration to the completion of the batch or stored procedure in which it is announced. Unlike the various other SQL data types, users cannot utilize a table variable as input or an output parameter. In other words, if a table variable is ranged and defined to the stored procedure just like any regional variable they define with a DECLARE statement.

Consider the Code Below

Here “strName” variable outside if-block is having global scope, and the same name variable inside if-block is having its 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 do not have their own scope. If we write the above C# code in SQL it’ll result in an error because the BEGIN-END statement does not have its own scope and we have already defined variable @@strName earlier.

How Scope Works in SQL?

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

Let’s take the same example using the GO keyword.

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

Hiren
Maulik

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

Here, we wrote “GO 5”. So it repeated the same batch 5 times.

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

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.