Recently I had a very interesting situation at my client where I had to visit in person for Comprehensive Database Performance Health Check. Most of my performance tuning engagements are online but there are clients who can’t share their application screen online and I have to visit in person to organizations. During recent work, we had a very interesting situation of Dynamic SQL and Global Temporary Tables.
Let understand how Temporary Tables behave inside and outside of the Dynamic SQL.
Temp Tables Inside Dynamic SQL
First, see the following statement, it will work just fine because as temp table is accessed inside the sp_executeSQL.
DECLARE @SQLStatement NVARCHAR(1000); SET @SQLStatement = 'CREATE TABLE #TempTable (ID INT); SELECT ID FROM #TempTable;'; EXEC sp_executesql @SQLStatement;
Now let us take this example forward and move out the SELECT statement from the dynamic part of the SQL.
Temp Tables Outside Dynamic SQL
The following statement will give an error as the temp table is accessed outside the dynamic part of the SQL.
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’.
Now my client had a unique requirement where they have to use the SELECT statement outside the dynamic part of the SQL and it was really challenging for them. However, the solution to it is very simple and it is to use Global Temp Tables.
Global Temporary Tables Outside Dynamic SQL
When you create the Global Temporary tables with the help of double Hash sign before the table name, they stay in the system beyond the scope of the session. Here is an example where you can see that the global temp table created inside the dynamic SQL can be accessed outside the scope of it.
DECLARE @SQLStatement NVARCHAR(1000); SET @SQLStatement = 'CREATE TABLE ##GlobalTempTable (ID INT);'; EXEC sp_executesql @SQLStatement; SELECT ID FROM ##GlobalTempTable;
When you run the above script, you will not find any errors.
If you have any other interesting scripts or tips, please send me an email or leave a comment and I will be happy to publish them on the blog with due credit to you.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
I had a similar requirement to use temporary table in nested procedures for some calculations. Since the data in a temporary table won’t be available outside the session, i had to go with global temporary table option.
when we create the above illustrated query as stored procedure and accessed by multiple users from website. Will this cause any trouble, Since we are accessing global temp table?
CREATE TABLE #TempTable (ID INT);
DECLARE @SQLStatement NVARCHAR(1000);
SET @SQLStatement = ”INSERT INTO #TempTabld(ID) Select 20200819;
EXEC sp_executesql @SQLStatement;
SELECT ID FROM #TempTable;