How to check if stored procedure exists or Not in SQL server 2008R2 ?

Drop and Create Stored Procedure in SQL before Creating any SP:


In my project I need to give some Stored Procedure to client system to run some bug fixing. The client environment already have the same SP name. So we can give directly ALTER SP query to execute in the SQL server. But that not a good practices. As a good developer need to think future also. What happen if new client dos not have the Stored Procedure in that system? It will give error message. So avoiding the Error Message we need to add little bit code before the SP.

Before creating SP or deleting any Stored Procedure we need to check if that SP already exist or not in the Database. If Exist DROP the procedure and recreate again. If not exist that SP means don’t Drop the SP just create new Stored Procedure. This is the Good way to Creating Script in the SQL server. Below example first I have checked that SP name already Exist in the DB or Not. If exist I will DROP the Procedure and after that I will create New SP. If Not exist that SP name means it will not run the Drop function.

Drop Stored Procedure if exist in SQL Server and Create New one:

--My_SP_Name - Stored Procedure Name

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[My_SP_Name]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[My_SP_Name]

GO

CREATE PROCEDURE [dbo].[My_SP_Name]

@STARTDATE DATETIME,

@ENDDATE DATETIME

AS

GO

I hope the above code will help you to make good SQL script wit out error free script running. This is not only for SPs, You can use same method for Create Table also.