CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
Nishant Chauhan
Nishant Chauhan

Reputation: 83

How to check if a stored procedure exists in sql server

Please help I want to check if a stored procedure exists in my database. If it does exist, then that stored procedure should be dropped, and if it does not exist then the stored procedure should be created.

Please tell me query

This is my attempt so far:

try {
objData.Query = "if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].["+ ProcName+"]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
drop procedure [dbo].[ "+ ProcName+"]" ; // 
objData.Query = " Drop Proc " + ProcName;
objData.Execute(); 
} catch 
{
}

Upvotes: 2

Views: 22380

Answers (2)

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

IF OBJECT_ID(N'[dbo].[spa_Search]', N'P') IS NOT NULL
    DROP PROCEDURE [dbo].[spa_Search]
GO

You can check OBJECT_ID which is object identification number of a schema-scoped object.

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]   
  object_name' [ ,'object_type' ] )

Arguments
' object_name '
Is the object to be used. object_name is either varchar or nvarchar. If object_name is varchar, it is implicitly converted to nvarchar. Specifying the database and schema names is optional.

' object_type '
Is the schema-scoped object type. object_type is either varchar or nvarchar. If object_type is varchar, it is implicitly converted to nvarchar. For a list of object types, see the type column in sys.objects (Transact-SQL)

Return Types
int

Exceptions
For a spatial index, OBJECT_ID returns NULL.
Returns NULL on error.
A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_ID may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.

For quick view on OBJECT_ID

Upvotes: 7

dipi evil
dipi evil

Reputation: 517

Not sure about your enviroment... But in SQL Server 2000 you can try this BEFORE start the script for your procedure:

IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'P' and name = 'TheNameOfYourProcedure')
BEGIN
   DROP PROCEDURE TheNameOfYourProcedure
END
GO

Upvotes: 4

Related Questions