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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-18 : 09:22:04
|
| Chris writes "I am new to SQL Server and I have the following problem: I have a stored procedure that must delete another stored procedure. I use the drop procedure command. An error occcurs however when the stored procedure is run and the one being deleted doesn't exist. So my question is can I detect if a stored procedure exist and if it does drop it? Thank you." |
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2002-06-18 : 09:26:43
|
| if exists ( select * from dbo.sysobjects where id = object_id('dbo.spSomeStoredProcedure') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure dbo.spSomeStoredProceduremacka. |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-18 : 09:27:17
|
you can query sysobjects and check for its existence.something on this lines.if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[countrysales]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)SNIPED -------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo EmersonEdited by - Nazim on 06/18/2002 09:27:50Edited by - Nazim on 06/18/2002 09:39:02 |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-06-18 : 09:28:52
|
Hi!Try this if-clause:if exists (select * from dbo.sysobjects where id = object_id('name') and OBJECTPROPERTY(id, 'IsProcedure') = 1)drop procedure nameIt checks if the procedure exists. Replace name with your proc name of course :)regards,Kalle DahlbergLOL  Edited by - andraax on 06/18/2002 09:29:45 |
|
|
|
|
|
|
Stored Procedures