Thursday, December 6, 2012

Checking a Stored Procedure exists or not in database


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

Thanks,
Hemant

No comments:

Post a Comment