Blog

Checking a Stored Procedure exists or not in database

Posted by Hemant Srivastava Blogs | SQL Nov 29, 2012
A SQL script that checks a strored procedure exists or not . If it is, it deletes the stored proc before running its creation script.

When we keep all our stored procedures creation script in a file and want to run that whole script. It always complains about already existing stored procedure having same name.

 

It is better before running your Stored Proc creation script, your creation script should be smart enough that always checks the presence of same name stored procedure before.

 

Following piece of SQL Script checks the stored procedure presence. If it finds, it delets the old one and create a new one having same name (of course most of the time, the new one would be modified)

When we keep all our stored procedures creation script in a file and want to run that whole script. It always complains about already existing stored procedure having same name.

 

It is better before running your Stored Proc creation script, your creation script should be smart enough that always checks the presence of same name stored procedure before.

 

Following piece of SQL Script checks the stored procedure presence. If it finds, it delets the old one and create a new one having same name (of course most of the time, the new one would be modified)


 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StoredProcedureName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[ StoredProcedureName]

GO

ChkStoredProcPresence.jpg

COMMENT USING
PREMIUM SPONSORS
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.
SPONSORED BY
  • MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.