June 24, 2004 at 6:17 am
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
June 24, 2004 at 6:26 am
You need to create the #temp table before you execute the SP_ExecuteSQL.
June 24, 2004 at 6:36 am
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
June 24, 2004 at 6:52 am
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
June 24, 2004 at 7:00 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply