Search

Friday, January 21, 2011

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

IF EXISTS(SELECT * FROM dbo.sysobjectsWHERE id = object_id(N'[dbo].[YourStoredProcName]')and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[YourStoredProcName]GOCREATE PROCEDURE dbo.YourStoredProcNameAS-- Logic Comes HereGO
Update: A BETTER solution suggested by Madhivanan
IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1DROP PROCEDURE [dbo].[YourStoredProcName]GOCREATE PROCEDURE dbo.YourStoredProcNameAS-- Logic Comes HereGO

The syntax shown above will drop a stored procedure if it exists and recreate it.
Posted by at
Labels:

2 comments:

  1. AnonymousJune 18, 2011 at 3:00 AM

    These do drop and recreate the procedure but then you would lose any permissions assigned to it.

    Is there a way to check to see if it exists and if it does then change from a CREATE to an ALTER?

    ReplyDelete
  2. UnknownJune 18, 2011 at 12:39 PM

    You can do this also

    IF EXISTS(SELECT * FROM dbo.sysobjectsWHERE id = object_id(N'[dbo].[YourStoredProcName]')and OBJECTPROPERTY(id, N'IsProcedure') = 1) GO ALTER PROCEDURE dbo.YourStoredProcNameAS-- Logic Comes Here GO

    ReplyDelete

Subscribe to: Post Comments (Atom)