A blog about SQL, technology, and careers in technology.

Main menu

Post navigation

Variable Scope in SQL Server

The way variable scoping works in SQL Server has been well documented on MSDN, but I have seen related issues come up on forums recently so I decided to go over it. Generally, a variable in T-SQL is available between when it has been declared and the end of the batch, function, or stored procedure that it is defined in. That is of course fairly straightforward, but it is easy to miss some of the subtleties on what constitutes a batch.

Go creates a new batch.

The most explicit way to end a batch is with the GO command. So for instance:

Each Execute is in a new batch.

When using SSMS, every call to execute generates a new batch. So, for instance if you highlight:

declare @var varchar(10)
set @var = 'Hi'
print @var --This works

In SSMS and press F5 (or Execute under the Query Menu) then it is all one batch and the print statement will execute. If you then immediately highlight that print statement again and press F5 again, it will give the “Must declare the scalar variable” because it is executing in a new batch.

A transaction can include multiple batches.

An explicit transaction can include multiple batches. So, you can open an explicit transaction, declare a variable within it, start a new batch and so lose access to the variable before the transaction commits. So, to re-use the first example with a slight tweak:

begin transaction
declare @var varchar(10)
set @var = 'Hi'
print @var --This works
GO --End the batch, but not the transaction
print @var --Generates an error since out of batch
--this particular error won't roll back the transaction, but ends the batch

It got that error because the GO command ended the batch. The Level 15 error generated ends the execution but not the transaction, so looking @@trancount at this point will return one and we could proceed to commit or rollback the transactions (which doesn’t actually matter in this case since it didn’t affect anything).

A procedure has a different scope even if called within the same batch.

Even if it is called as part of a longer batch, a procedure has its own scope. So a procedure can’t directly access other variables in a batch that aren’t passed to it as a parameter and it can’t affect variables outside of it except through output parameters. So if we create a procedure just for testing like: