Exec producing table out of scope

Question

Exec producing table out of scope

olibbhq

SSCarpal Tunnel

Points: 4034
More actions
December 17, 2013 at 7:56 am

#302463

Hi,
I am generating some dynamic sql which I would like to run and return the data from, however when I run EXEC(@TEMP_TABLE_STRING) it cannot return the table I think because it is out of scope.
Is there a way around this.
declare@Tablename VARCHAR(150) = 'TEMP_LOCATIONS'
declare@FilterClause VARCHAR(512)=NULL
declare@Classes VARCHAR(100)=NULL
declare@IsExcel BIT = 1
DECLARE @sql Nvarchar(MAX) = ''
SET NOCOUNT ON;
DECLARE @TEMP_TABLE_STRING NVARCHAR(MAX)
SET @TEMP_TABLE_STRING =
CASE WHEN @IsExcel = 1 THEN
'SELECT TOP(65000) WC.* '
ELSE
'SELECT WC.* '
END
+ ' INTO #YourTable FROM dbo.V_ALLCLASSESWCOMMENTS wc JOIN '+ @Tablename +' loc ON wc.LOCATION_ID = loc.LOCATION_ID '
+ CASE WHEN @FilterClause IS NOT NULL THEN ' WHERE '+ @FilterClause ELSE '' END
EXEC(@TEMP_TABLE_STRING)
SELECT * FROM #YourTable
Returning the error:
Msg 208, Level 16, State 0, Line 22
Invalid object name '#YourTable'.
Many Thanks for your help in advance.
Oliver

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yup, scoping means that the temp table will be dropped automatically as soon as the dynamic SQL finishes. Way around that is to create the temp table outside the dynamic SQL and have the dynamic SQL be an insert into.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 2

Think I have it, create an empty table first, then with the EXEC use insert into.

This works, if it is poor SQL please let me know,

Many Thanks,

Oliver

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 3

Think we were writing at the same time!

Thanks for the help

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

By the way, you might want to do some reading on SQL Injection and why your code is a security vulnerability as written.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass