We tend to forget the most basic syntax of all and one of them is to check if a stored procedure exists and then drop it. Here’s the syntax for your reference:
IF EXISTS
(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[YourStoredProcName]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS
-- Logic Comes Here
GO
Update: A BETTER solution suggested by Madhivanan
IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS-- Logic Comes Here
GOThe syntax shown above will drop a stored procedure if it exists and recreate it.
About The Author
7 comments:
Suprotim,
As object_id looks for entry at sysobjects, you dont need to query it against sysobjects. You can simply write
if OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS-- Logic Comes Here
GO
Yes indeed! The post has been updated. Thanks!
Suprotim,
Following code will also work the same way..
if object_id('dbo.YourStoredProcName','p') is not null
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS-- Logic Comes Here
GO
The code that Chintak posted is the easiest, in my opinion.
However, when you DROP a procedure and then re-CREATE it, you lose any permissions that may have been created against it.
What many people do instead is to first create a dummy procedure if it doesn't exist, and then use ALTER PROCEDURE (since ALTER PROCEDURE keeps all permissions in place).
In other words...
IF OBJECT_ID('MyProcedure','P') IS NULL
EXEC ('CREATE PROC MyProcedure AS SELECT 1')
GO
ALTER PROC MyProcedure AS ...
--Brad
Chintak: Thanks for the alternate code.
Brad: Great feedback! I really like the ALTER PROCEDURE trick!
I always wonder WHY you check if it's a procedure.
Assume "schema.name" is a non-procedure object in the database. Say, a view.
If you run "if object_id(N'schema.name') is not null drop procedure schema.name", it will fail because schema.name is a view, not a procedure.
But if you run "if object_is(N'schema.name','p') is not null drop procedure schema.name" it won't try to drop the view, but your CREATE PROCEDURE schema.name will fail.
Either way, it fails. You go out of your way to check if schema.name is a procedure, for no gain. If it exists and it is a procedure, "if object_id(N'schema.name') is not null drop procedure" will work.
It is pointless to check if the object is a procedure or not.
Assume schema.name is a view, not a procedure.
"if object_id(N'schema.name','p') is not null drop procedure schema.name" will not try to drop anything, but your CREATE PROCEDURE schema.view will fail because you can't have TWO objects schema.name.
If you use just "if object_id(N'schema.view') is not null drop procedure schema.view" will fail when you try to "drop procedure" on a view.
They both fail if schema.name is a non-procedure, they both succeed if schema.name IS a procedure. There is zero benefit in checking of schema.name is a procedure or not.
Post a Comment