T-SQL insert into temporary table from dynamic SQL

Question

This is running on SQL Server 2008, but the database is in SQL Server 2000 compatibility mode (this I cannot change).

A temporary table is created at the beginning of the stored procedure and then insert into via an EXEC statement from dynamically generated SQL. How is this executing successfully, even though the temporary table (should be, from my understand) is out the execution scope, or does this get bypassed temporarily when performing these statements inside a stored procedure?

Example below:

CREATE PROCEDURE myProc (Param1 int....)
AS
BEGIN
   Declare @SQL varchar(max) = null

   Create table #tempTable 
   (
       ID int,
       Code1 varchar(255),
       ...
   )

   SET @SQL = 'insert into #tempTable '
              + ...
   EXEC(@SQL)

   Select * from #tempTable
END

Any help understanding this would be greatly appreciated!

Thanks!

Temporary tables have dynamic scope, not lexical scope.

Shannon Severance
– Shannon Severance

2015-09-17 21:42:12 +00:00
Commented Sep 17, 2015 at 21:42 — Shannon Severance
– Shannon Severance, Commented Sep 17, 2015 at 21:42

Lukasz Szozda · Accepted Answer · 2015-09-17 18:12:00Z

3

Shortly: The temporary table created in the outer batch is accessible inside the dynamic SQL but not vice versa.

See doc

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

edited Sep 17, 2015 at 18:12

answered Sep 17, 2015 at 18:06

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL insert into temporary table from dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related