why this SP is not working?????????? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

why this SP is not working??????????

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-02 : 08:37:08

hi..why this SP is not working that giving me error like

Server: Msg 2714, Level 16, State 1, Procedure test_E, Line 12
There is already an object named '#emp' in the database.

my one of the developer used this every where... what should i do if i dont wanna to change any of the code...i wanna some how work this SP...

create proc test_E
as

select * into #emp
from (select * from emp) p

select * from #emp

drop table #emp

select * into #emp
from (select * from dept) p

select * from #emp

drop table #emp

T.I.A

Papillon

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-03-02 : 08:40:44

Open Query Analyser making sure you are in the same Database as your Stored Procedure and run:-

drop table #emp

Then try your Stored Procedure again.

Pat.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-02 : 08:41:00

Is this the actual code for the proc?

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-02 : 08:47:04

hi..its just sample to understand....

i opened another QA and i was in the same database as my SP is..but when i ran
drop table #emp

i got this error

Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#emp', because it does not exist in the system catalog.

T.I.A

Papillon

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 08:47:26

[code]create proc test_E
as

select * into #emp
from (select * from emp) p

select * from #emp

drop table #emp

select * into #emp
from (select * from dept) p

select * from #emp

drop table #emp[/code]

You can't use SELECT ... INTO #emp twice. The parser will not let you pass this. Change to insert ... select or use different temp table name

----------------------------------
'KH'

X002548
Not Just a Number

15586 Posts

Posted - 2006-03-02 : 09:08:37

A veritable WTF...someone should alert the media

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources