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:
- Can an Index Reduce Performance of SELECT Query? – Interview Question of the Week #234
- SQL SERVER – SQL Agent Job and Backslash – Strange Behavior
- SQL SERVER – Swap Column Values In Table
- Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class
Reference: Pinal Dave (https://blog.sqlauthority.com)