July 09, 2010

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

5 Responses to "Check If Stored Procedure Exists, Else Drop It and Recreate – SQL Server"
  1. Madhivanan said...
    July 9, 2010 at 4:12 AM

    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

  2. Suprotim Agarwal said...
    July 9, 2010 at 4:16 AM

    Yes indeed! The post has been updated. Thanks!

  3. Chintak said...
    July 9, 2010 at 11:44 PM

    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

  4. Brad Schulz said...
    July 12, 2010 at 11:53 AM

    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

  5. Suprotim Agarwal said...
    July 12, 2010 at 8:47 PM

    Chintak: Thanks for the alternate code.

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions