Problem
Every time a client uses the “database management” functionality, I have to run a SQL procedure. Creating stored procedures on the client database is part of the script. When the script is run, some of these clients may already have the stored procedure installed, while others may not. I need the missing stored procedures added to the client database, however no matter how I bend T-SQL syntax, I get an error.
I’ve heard that dropping before generating works is a good idea, but I prefer not to do it that way.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
DROP PROCEDURE MyProc
GO
CREATE PROCEDURE MyProc
...
How can I add a check for the presence of a stored procedure, create it if it doesn’t exist, but change it if it does?
Asked by The Shaper
Solution #1
I realize this has already been answered, but this is how we used to do it:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.MyProc'))
exec('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER PROCEDURE [dbo].[MyProc]
AS
....
Just to keep the technique from being abandoned.
Answered by Geoff
Solution #2
Procedural code can be executed anywhere a query can be executed.
Simply paste everything after AS:
BEGIN
DECLARE @myvar INT
SELECT *
FROM mytable
WHERE @myvar ...
END
This code performs the same functions as a stored procedure, but it is not stored in the database.
In PL/SQL, this is referred to as an anonymous procedure.
Update:
The title of your query is a little perplexing.
Your code is good if you only need to construct a procedure if one does not already exist.
In the create script, SSMS produces the following output:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'myproc')
AND type IN ( N'P', N'PC' ) )
DROP …
CREATE …
Update:
When including the schema, here’s an example of how to accomplish it:
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[MyProc]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE [dbo].[MyProc]
END
The schema in the example above is dbo.
Update:
You may simply accomplish this with SQL Server 2016+.
ALTER OR CREATE THE PROCEDURE dbo. MyProc
Answered by Quassnoi
Solution #3
Here’s one approach to check for the presence of a database object before removing it (example uses an SPROC, same like your example above but could be adapted for tables, indexes, and so on…):
IF (OBJECT_ID('MyProcedure') IS NOT NULL)
DROP PROCEDURE MyProcedure
GO
This is quick and elegant, but you must ensure that all object types have unique names, as it does not take this into account.
I hope this information is useful!
Answered by MrChips
Solution #4
Although I understand your desire to “ALTER a method if it exists and construct one if it does not,” I believe it is more straightforward to:
Like this:
IF OBJECT_ID('MyProcedure', 'P') IS NOT NULL
DROP PROCEDURE MyProcedure
GO
CREATE PROCEDURE MyProcedure AS
BEGIN
/* ..... */
END
GO
The second option instructs OBJECT ID to only check for stored procedures with object type = ‘P’:
You can receive a complete list of alternatives by going to:
SELECT name
FROM master..spt_values
WHERE type = 'O9T'
Answered by Michael Currie
Solution #5
You can use the new DROP PROCEDURE IF EXISTS feature in SQL SERVER 2016. [IF EXISTS] DROP PROC | PROCEDURE procedure [schema name.] [,,,n]
Reference : https://msdn.microsoft.com/en-us/library/ms174969.aspx
Answered by Hybris95
Post is based on https://stackoverflow.com/questions/2072086/how-to-check-if-a-stored-procedure-exists-before-creating-it