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

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

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

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 */
    @i TINYINT = 0
    , @j TINYINT = 3;

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

-- Confirm that the table was extended outside the dynamic SQL

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.

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: