Tag Archives: temp table

Temp Table Scope, Part 2

In a previous post we discussed the scope of temporary tables; specifically, their persistence inside and outside of dynamic SQL statements within the same connection. In that post we examined using a global temporary table to resolve the persistence issue. In this post, we’ll look at an alternate approach: creating a basic table first and then extending it through dynamic SQL. Let’s get started!

As you’ll recall, we previously demonstrated that temporary tables created within dynamic SQL were not available outside of the dynamic SQL execution. However, a temporary table created within a connection is available within a dynamic SQL context. The following script confirms this:

-- If the temp table we intend to create already exists, drop it
IF OBJECT_ID(N'tempdb..#tbl') IS NOT NULL
    DROP TABLE #tbl;

-- Create a basic temp table
CREATE TABLE #tbl(ID INT IDENTITY(1,1) NOT NULL);

-- Confirm the temp table was created
SELECT 'ObjectID' = OBJECT_ID(N'tempdb..#tbl');

-- Confirm the temp table is accessible within dynamic SQL
DECLARE @SQL NVARCHAR(2000) =
    N'SELECT ''ObjectID'' = OBJECT_ID(N''tempdb..#tbl'');';
EXECUTE (@SQL);

You will notice that the object id of the temporary table is the same both outside and inside the dynamic SQL. In other words, the temporary table is persistent across the connection, unlike when a temporary table is created within dynamic SQL.

Now that we’ve seen the temporary table persists into dynamic SQL, we can extend the table within the dynamic SQL statement.

/* Extend the temp table via dynamic SQL
In this instance we'll add a parameterized number of columns */
DECLARE
    @i TINYINT = 0
    , @j TINYINT = 3;

WHILE @i < @j
BEGIN
    SET @i += 1;
    SET @SQL = N'ALTER TABLE #tbl ADD Col'
        + CONVERT(NVARCHAR(10), @i)
        + N' VARCHAR(50) NULL;';
    EXECUTE (@SQL);
END

-- Confirm that the table was extended outside the dynamic SQL
SELECT * FROM #tbl;

As the query result shows, the columns were successfully added to the temporary table. And, what’s more, the temporary table is available both inside and outside of a dynamic SQL context. Bob’s your uncle.