Log Shipping – NORECOVERY vs. STANDBY mode

I was recently came across a question in one of the user forums where  a stored procedure that required using both temporary tables and dynamic SQL.  Seems like user wasn’t aware of the fact that the dynamic SQL creates a new scope making the temporary unavailable to the parent scope. 

To demonstrate this consider the following statement:

USE AdventureWorks2012;
GO

DECLARE @cmd NVARCHAR(400)
SELECT @cmd = 'SELECT FirstName, 
  MiddleName, 
  LastName
   INTO #temp
   FROM Person.Person 
   SELECT *
   FROM #temp'
EXEC sp_executesql @cmd

SELECT *
FROM #temp;
GO

 

Executing this statement results in 19,972 rows being inserted into the #temp temporary table and then returning those 19,972 rows from the select statement embedded within the dynamic SQL statement, however an error message is returned from select query outside of the dynamic SQL:

msg 208, level 16, state 0, line 12

invalid object name ‘#temp’.

The reason for this is the fact that the scope of the temporary table is within the dynamic SQL and not the outer parent query.  Fortunately the fix is quite simple, rather than using SELECT INTO to dynamically create the temporary table explicitly create the #temp table using DDL outside of the dynamic SQL and then use an INSERT statement to populate the table within the dynamic SQL.  The below query demonstrates how explicitly creating the table and using dynamic SQL to populate it insures that the table remains in scope.

 

USE AdventureWorks2012;
GO

CREATE TABLE #temp(
FirstName  NVARCHAR(50),
MiddleName NVARCHAR(50),
LastName   NVARCHAR(50))

DECLARE @cmd NVARCHAR(400)
SELECT @cmd = ‘INSERT #temp
   SELECT FirstName, 
  MiddleName, 
  LastName
   FROM Person.Person 
   SELECT *
   FROM #temp’
EXEC sp_executesql @cmd

SELECT *
FROM #temp;
GO

 

 

 

 

Another option is to use global temporary tables, but unfortunately deploying this as a stored procedure would cause the error “Object already exists” if two users were to execute the procedure at the same time.

One common reason that dynamic SQL is needed in the first place is that the columns of the temp table are variable. So while the solution above (moving the CREATE TABLE statement outside of the dynamic SQL) is the preferred solution, it sometimes is not possible.

A couple other workarounds in this case:

1) Use a global temp table (as mentioned above), with a GUID embedded in the table name to avoid the concurrency issue highlighted in this post.

2) Push ALL the work needed to be done on the temporary table into the single block of dynamic SQL, so that losing access to the variable after the dynamic batch is completed isn't an issue.