We tend to forget the most basic syntax of all and one of them is to check if a stored procedure exists and then drop it. Here’s the syntax for your reference:
IF EXISTS
(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[YourStoredProcName]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS
-- Logic Comes Here
GO
Update: A BETTER solution suggested by Madhivanan
IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS-- Logic Comes Here
GOThe syntax shown above will drop a stored procedure if it exists and recreate it.
Did you like this post?
|
|
||
|
|
|
|
|
subscribe via rss |
|
subscribe via e-mail |
|
print this post |
|
follow me on twitter |
About The Author
comments
5 Responses to "Check If Stored Procedure Exists, Else Drop It and Recreate – SQL Server"Suprotim,
As object_id looks for entry at sysobjects, you dont need to query it against sysobjects. You can simply write
if OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS-- Logic Comes Here
GO
Yes indeed! The post has been updated. Thanks!
Suprotim,
Following code will also work the same way..
if object_id('dbo.YourStoredProcName','p') is not null
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS-- Logic Comes Here
GO
The code that Chintak posted is the easiest, in my opinion.
However, when you DROP a procedure and then re-CREATE it, you lose any permissions that may have been created against it.
What many people do instead is to first create a dummy procedure if it doesn't exist, and then use ALTER PROCEDURE (since ALTER PROCEDURE keeps all permissions in place).
In other words...
IF OBJECT_ID('MyProcedure','P') IS NULL
EXEC ('CREATE PROC MyProcedure AS SELECT 1')
GO
ALTER PROC MyProcedure AS ...
--Brad
Chintak: Thanks for the alternate code.
Brad: Great feedback! I really like the ALTER PROCEDURE trick!
Post a Comment