SQL SERVER – Dynamic SQL and Temporary Tables

July 22, 2019

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

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)