How can i delete a table from within a stored proc

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

SQL Server 2005 Forums

Transact-SQL (2005)

How can i delete a table from within a stored proc

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-11-10 : 04:56:00

Hi everyone,
If i run the following code :


IF EXISTS(SELECT * FROM sysobjects WHERE name='to_pros')
/*IF OBJECT_ID("to_pros") IS NOT NULL*/
DROP TABLE to_pros
GO

SELECT * INTO to_pros
FROM final_table
WHERE
LOWER(Date) NOT LIKE '%total%'

every thing is fine. I run it again and again and everything is just fine. No error message !
When that same code is part of a wider SP and I click the "Execute" button in the SSMS I get an error message
that says:

quote:

Msg 2714, Level 16, State 6, Line 2
There is already an object named 'to_pros' in the database.

I know there is a table named: "to_pros" in the database but there also is a line telling the procedure to
drop it !!!
if any one finds out why i get that error i'll be grateful !
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 05:01:15

To drop the table manually before creating the SP will help.

Once the SP is created, it will run fine.

No, you're never too old to Yak'n'Roll if you're too young to die.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-11-10 : 06:15:24

Hi Webfred,
Thanks.
What if i run that procedure from another procedure ? What if this procedure runs automatically ?
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 06:22:07

The posted problem only occurs while trying to create the procedure - not an runtime.
But be sure to run this procedure only one at a time in the same database.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 06:23:05

Do you know the difference between creating and executing a stored procedure?

No, you're never too old to Yak'n'Roll if you're too young to die.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-11-10 : 06:54:21

quote:
Originally posted by webfred

The posted problem only occurs while trying to create the procedure - not an runtime.
But be sure to run this procedure only one at a time in the same database.

No, you're never too old to Yak'n'Roll if you're too young to die.

It occcurs also when the SP is created. How can i run it if it is not created ? Why can i not run run it as many times as i wish ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 07:35:37

This is working fine!
--drop proc test
create procedure test
as
if (Object_ID('testsptable') is not null)
drop table testsptable

select 'test' as tst,4711 as tstid into testsptable where 1=1

exec test

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources