INF: Scoping of Temporary Objects with EXEC

View products that this article applies to.

This article was previously published under Q131913

Expand all | Collapse all

SUMMARY

Global temporary tables and EXEC of string commands are new features available in Microsoft SQL Server version 6.0 Transact-SQL. The scoping of temporary and global temporary tables and procedures can be a cause for concern with developers developing for SQL Server 6.0.

MORE INFORMATION

## tables are "global" no matter where they are declared. Their lifetime is until the session that declared them either exits or explicitly drops them. If another user is referencing a global table, for example, actively selecting from it when the creator exits, the dropping of the table will be deferred until the user referencing the table is done with it; after the SELECT or UPDATE completes.

# tables are analogous to local variables. They are only visible to the declaring scope and all subordinate scopes. Their lifetime ends when the declaring block goes out of scope. If you have created the table at level 0 and referenced it using EXEC ('select ...') from level 1. Because the local temporary table was declared at level 0, it will exist until explicitly dropped or the session exits, (in which case it will be dropped).

Both stored procedures and EXEC "string" create a new nested scope. You can see this using the @@nestlevel variable.

--**** Top level nesting level is 0
1> select "Nesting level is " + str (@@nestlevel)
2> go
 ---------------------------
 Nesting level is          0

--**** Try nesting level in a sproc.
1> create proc #nestlevel as
2>     select "Nesting level is " + str (@@nestlevel)
3> go

1> #nestlevel
2> go
 ---------------------------
 Nesting level is          1

--**** Here is an interesting one. If we use the EXEC ('string')
--**** syntax, it introduces another nesting level. That means that
--**** any local temporary tables that we defined inside
--**** EXEC ('string') will be dropped when the EXEC is completed.
1> exec ('#nestlevel')
2> go
 ---------------------------
 Nesting level is          2

--**** Note that EXEC proc syntax doesn't result in a new level.
1> exec #nestlevel
2> go
 ---------------------------
 Nesting level is          1