SybaseBlog

Is there scoping in T-SQL? Actually ... no, not really.

The term "scope" refers to the time or place that the value of a declared variable is accessible. Normally variables declared in a loop, for instance, are not available outside that loop.

In T-SQL, a local variable is available basically anywhere in the stored procedure. There are also global variables, which endure beyond the session and can be referenced by other procedures or triggers. So I suppose the variables are scoped, but only by stored procedure/trigger.

The situation gets slightly more complex when it involves cursors, but the idea is still the same. For example, here is a quote from the Sybase manual:

"If a declare cursor statement is part of a stored procedure or trigger, the cursor created within it applies to that scope and to the scope that launched the stored procedure or trigger. However, cursors declared inside a trigger on an inserted or a deleted table are not accessible to any nested stored procedures or triggers. Such cursors are accessible within the scope of that trigger. Once the stored procedure or trigger completes, Adaptive Server deallocates the cursors created within it. "

Here is a test that demonstrates the relative non-scoping of T-SQL variables.

dump tran sybsystemprocs with truncate_only
go

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_testscope" and type = 'P')
drop proc sp_testscope
go

-- create test procedure
create procedure sp_testscope
as
-- Step 1: Create a variable inside
if (1=1)
begin
declare @scoped_var int
select @scoped_var = 1
print "*** Value of @scoped_var is %1! ***", @scoped_var
end

-- Step 2: Try to use it outside the scope
print "*** Value of @scoped_var is %1! ***", @scoped_var
select @scoped_var = 2
print "*** Value of @scoped_var is %1! ***", @scoped_var

-- Step 3: Try to declare one that will hide the other (this returns an error)
-- declare @scoped_var int
-- select @scoped_var = 3
-- print "*** Value of @scoped_var is %1! ***", @scoped_var
go

-- grant execute
if object_id('sp_testscope') is not null
begin
print '*** Created procedure dbo.sp_testscope ***'
grant execute on dbo.sp_testscope to public
end
else
begin
print '*** Failed creating proc dbo.sp_testscope ***'
end
go

-- do the tests
exec sp_testscope
go

-- clean up
print '*** Deleting proc dbo.sp_testscope ***'
if exists (select * from sysobjects where name = "sp_testscope" and type = 'P')
drop proc sp_testscope
go