Temp Table in Dynamic Query using SP_Executesql

Question

Post reply

Temp Table in Dynamic Query using SP_Executesql

Arvind-57714

SSCrazy

Points: 2529
More actions
June 24, 2004 at 6:17 am

#108102

I am creating a temp table #temp from a dynamically built query executed using SP_ExecuteSQL.
Even After i have executed the dynamic query, Am not able to access the data in the #temp table.
give me the following error:
"Invalid object name '#temp."
Any ideas!
Cheers,

Arvind

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

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

Pascal Dobrautz SSC Enthusiast Points: 158 More actions · Answer 1

You need to create the #temp table before you execute the SP_ExecuteSQL.

----------------------------------------
Pascal Dobrautz
www.sqlassi.net

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 2

I am creating the #temp table in the Dynamic Query which is executed using sp_executesql. Can i not work this way....if so whats the constraint in it?

Cheers,

Arvind

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 3

To access and view the temp table you must do it from inside the dynamic query.

For Example:

EXEC ('CREATE TABLE #Temp (ID INT) SELECT * FROM #Temp')

Would work, but not

EXEC ('CREATE TABLE #Temp (ID INT)')

SELECT * FROM #temp

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 4

Thx for all the quick responses. Actually I kinda lost my plot here....I knew the issue..Here is how it goes...I think every Exec() or SP_ExceuteSQL statement has a scope of its own. In other words tho they are used in the same sp they cannot be used for further processing. Correct me if i am wrong abt this.

I solved this by using Global Temp Tables. ##temp...works like a gem.

Cheers,

Arvind