INF: Scoping of Temporary Objects with EXEC

Last reviewed: April 30, 1997
Article ID: Q131913

The information in this article applies to:

Microsoft SQL Server, version 6.0

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

Additional query words: sql6 exec temp tables scope
Keywords : kbother SSrvTran_SQL SSrvWinNT
Version : 6.0
Platform : WINDOWS
Resolution Type : kbcode

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.