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)
5 Comments. Leave new
What about ## temp tables? Those can be generated dynamically and referenced after the executed query.
I’ve used modified guid strings as names for these global temp tables to avoid possible contention. Is there a better way?
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
In my scenario, the temp table itself needs to be dynamic because its definition isn’t fixed. So I have several dynamic sql strings to execute in the same procedure to handle the same operations on different tables. First I create a temp table, then I insert records into it, then I delete those records from the main table and finally I return the contents from the temp table to the client. But it isn’t recognizing the temp table name.
TempTable Inside Ad-Hoc SQL – Error
Msg 208, Level 16, State 0, Line 9
Invalid object name ‘#TempTable’.
The correct syntax is:
IF OBJECT_ID(‘tempdb..##temp1’) IS NOT NULL
DROP TABLE ##temp1
BEGIN
DECLARE @SQL_Str VARCHAR(MAX) =
‘
CREATE TABLE ##temp1
(
Col1 VARCHAR(1),
Col2 VARCHAR(1)
)
‘
EXECUTE (@SQL_Str)
— If you want to check output created by dynamic SQL, comment above execute and use print as below.
— PRINT @SQL_Str
END
— SELECT * FROM ##temp1
Thank you,
Parth Shah