Check If Stored Procedure Exists, Else Drop It and Recreate – SQL Server

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
GO

The syntax shown above will drop a stored procedure if it exists and recreate it.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

5 comments:

Madhivanan said...

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

Suprotim Agarwal said...

Yes indeed! The post has been updated. Thanks!

Chintak said...

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

Brad Schulz said...

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

Suprotim Agarwal said...

Chintak: Thanks for the alternate code.

Brad: Great feedback! I really like the ALTER PROCEDURE trick!