When using dynamic sql to create a temp table, you need to create the temp table first, then alter columns in the dynamic sql
see this blog below:
Temporary Tables and Dynamic SQL
When programming in SQL on occasion there is a need to generate temporaty tables inside a sproc without knowing the colums or type when the sproc is written. One such situation would be when you want to pivot a table. You can create a cursor over a column result set and create a new column for each value.
When you try to create temporary tables with dynamic SQL you will run into a scoping problem.
for example:
DECLARE @SQL nvarchar(4000)
SELECT @SQL = 'CREATE TABLE #Temp (col1 int)'
EXEC (@SQL)
SELECT * FROM #Temp
This will cause an error:
Msg 208, Level 16, State 0, Line 4
Invalid object name '#Temp'.
The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed.
One solution I have found for this problem is creating the table in the "parent" scope and then just using dynamic sql to modify the table. For this to work a table is created with a minimum set of colums. And then we use the ALTER TABLE statement with dynamic SQL. The Child session has access to the objects created in the parent session so the table can be modified with dynamic sql:
DECLARE @SQL NVARCHAR(4000)
CREATE TABLE #Temp ( id int null)
SELECT @SQL = 'ALTER #Temp ADD Col1 int null'
EXEC (@SQL)
SELECT * FROM #Temp
DROP TABLE #Temp
This table is visible and both columns will show up.
