SQL SERVER – Dynamic SQL and Temporary Tables

Followup question to previous blog posts is always fun and interesting. Earlier on this blog, I had written about SQL SERVER – Dynamic SQL and Global Temporary Tables, I received following follow up question about Dynamic SQL and Temporary Tables.

“I see that temporary tables created in the dynamic SQL can’t be accessed outside the scope but my requirements are totally opposite, I need to access the Temporary Table in the Dynamic SQL which is outside it. Is it possible?”

The answer in one word is – “Yes, that is possible!

Dynamic SQL – Ad-Hoc SQL

SQL SERVER - Dynamic SQL and Temporary Tables dynamic-800x449

Solarwinds

Let us understand that with the help of the Demonstration.

TempTable Inside Ad-Hoc SQL – Error

Let us see the following demonstration, where when we create Temp Table inside ad-hoc SQL, it gives us an error.

DECLARE @SQLStatement NVARCHAR(1000);
SET @SQLStatement = 'CREATE TABLE #TempTable (ID INT);';
EXEC sp_executesql @SQLStatement;
SELECT ID FROM #TempTable;

When you run the above statement it will give us the following error:

Msg 208, Level 16, State 0, Line 9
Invalid object name ‘#TempTable’.

The solution to this situation is to use Global Temporary Tables.

TempTable outside Ad-Hoc SQL – Works

Let us see the following demonstration, where when we create Temp Table outside ad-hoc SQL, it works just fine.

CREATE TABLE #TempTable (ID INT);
DECLARE @SQLStatement NVARCHAR(1000);
SET @SQLStatement = 'SELECT ID FROM #TempTable;';
EXEC sp_executesql @SQLStatement;

If you create a temp table in outside Ad-Hoc SQL, you can easily access that in the part of your ad-hoc SQL and solve the problem.

Let me know if you have any further question about this topic. I personally love little SQL Server Tips and Tricks. If you have any such tricks, please post that in the comment and I will publish it with the due credit to you.

Here are a few other interesting blog posts:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – Dynamic SQL and Global Temporary Tables
Next Post
SQL SERVER – Number of Rows Read Per Threads in Parallel Operations

Related Posts

3 Comments. Leave new

  • What about ## temp tables? Those can be generated dynamically and referenced after the executed query.

    Reply
  • Stuart Goodrick
    July 24, 2019 1:18 am

    I’ve used modified guid strings as names for these global temp tables to avoid possible contention. Is there a better way?

    Reply
  • BEGIN
    — Fix/Workaround/Solution:
    IF OBJECT_ID(‘tempdb.dbo.#temp’, N’U’) IS NOT NULL
    DROP TABLE dbo.#temp;

    CREATE TABLE #temp(id INT )
    END

    Reply

Leave a Reply

Menu