How To Drop A Table From A Stored Procedure Only If It Exists

Question

question

David 2 1 asked Dec 16, '09

How To Drop A Table From A Stored Procedure Only If It Exists

Is it possible to specify from within a stored procedure to ignore the error produced by trying to drop a table although it doesn't exist yet?

For example, if you create and run the below it will output an error the first time it is run:

create procedure test_drop
as
drop table dropthis
create table dropthis(
col1 varchar(10))

exec test_drop

Server: Msg 3701, Level 11, State 5, Procedure test_drop, Line 3 Cannot drop the table 'dropthis', because it does not exist in the system catalog.

Thanks in advance.

t-sql sql-server-2000

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-12-16T09:11:48Z

Madhivanan answered Dec 16, '09

create procedure test_drop            
as            
If exists(select * from sysobjects where xtype='u' and name='dropthis')            
drop table dropthis            
            
create table dropthis(            
col1 varchar(10))             
            
GO            
            
exec test_drop

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question