Dynamic SQL with Temp Table (HOW?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Dynamic SQL with Temp Table (HOW?)

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2004-08-24 : 08:19:46

Hi guys,

How can I solve this problem? I need to write a Dynamic SQL with Temp Table but when I try to execute the command, it gives me an "Invalid Object". Please help me.


Declare @SQL char(2000)
Set @SQL='Select top 10 * into #report from myTable'
exec (@sql)

Select * from #report

(10 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#report'.

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-24 : 08:27:25

you need to use global temp table -> ##temp

Declare @SQL char(2000)
Set @SQL='Select top 10 * into ##report from myTable'
exec (@sql)

Select * from ##report

Go with the flow & have fun! Else fight the flow :)

rpc86
Posting Yak Master

200 Posts

Posted - 2004-08-24 : 08:30:00

Thanks,

I have tried this before I posted my question, but it gave me the same error message.

rpc86
Posting Yak Master

200 Posts

Posted - 2004-08-24 : 08:31:25

Sorry,

I tried only single # but when I tried ## it worked.

Many Thanks !!!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-24 : 21:23:55

uh, you're not going to have multiple users using this same procedure at the same time are you?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources