How to check if a stored procedure exists before creating it
SqlSql ServerTsqlStored ProceduresSql Problem Overview
I have a SQL script that has to be run every time a client executes the "database management" functionality. The script includes creating stored procedures on the client database. Some of these clients might already have the stored procedure upon running the script, and some may not. I need to have the missing stored procedures added to the client database, but it doesn't matter how much I try to bend T-SQL syntax, I get
> CREATE/ALTER PROCEDURE' must be the first statement in a query batch
I've read that dropping before creating works, but I don't like doing 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 check for the existence of a stored procedure and create it if it doesn't exist but alter it if it does exist?
Sql Solutions
Solution 1 - Sql
I realize this has already been marked as answered, but we used to do it like this:
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 avoid dropping the procedure.
Solution 2 - Sql
You can run procedural code anywhere you are able to run a query.
Just copy everything after AS
:
BEGIN
DECLARE @myvar INT
SELECT *
FROM mytable
WHERE @myvar ...
END
This code does exactly same things a stored proc would do, but is not stored on the database side.
That's much like what is called anonymous procedure in PL/SQL
.
Update:
Your question title is a little bit confusing.
If you only need to create a procedure if it not exists, then your code is just fine.
Here's what SSMS
outputs in the create script:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'myproc')
AND type IN ( N'P', N'PC' ) )
DROP …
CREATE …
Update:
Example of how to do it when including the schema:
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[MyProc]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE [dbo].[MyProc]
END
In the example above, dbo is the schema.
Update:
In SQL Server 2016+, you can just do
CREATE OR ALTER PROCEDURE dbo.MyProc
Solution 3 - Sql
If you're looking for the simplest way to check for a database object's existence before removing it, here's one way (example uses a SPROC, just like your example above but could be modified for tables, indexes, etc...):
IF (OBJECT_ID('MyProcedure') IS NOT NULL)
DROP PROCEDURE MyProcedure
GO
This is quick and elegant, but you need to make sure you have unique object names across all object types since it does not take that into account.
I Hope this helps!
Solution 4 - Sql
I know you want to "ALTER a procedure if it exists and create it if it does not exist", but I believe it is simpler to:
- Drop the procedure (if it already exists) and then
- Re-create it.
Like this:
IF OBJECT_ID('MyProcedure', 'P') IS NOT NULL
DROP PROCEDURE MyProcedure
GO
CREATE PROCEDURE MyProcedure AS
BEGIN
/* ..... */
END
GO
The second parameter tells OBJECT_ID
to only look for objects with object_type = 'P'
, which are stored procedures:
> AF = Aggregate function (CLR) > > C = CHECK constraint > > D = DEFAULT (constraint or stand-alone) > > F = FOREIGN KEY constraint > > FN = SQL scalar function > > FS = Assembly (CLR) scalar-function > > FT = Assembly (CLR) table-valued function > > IF = SQL inline table-valued function > > IT = Internal table > > P = SQL Stored Procedure > > PC = Assembly (CLR) stored-procedure > > PG = Plan guide > > PK = PRIMARY KEY constraint > > R = Rule (old-style, stand-alone) > > RF = Replication-filter-procedure > > S = System base table > > SN = Synonym > > SO = Sequence object > > TF = SQL table-valued-function > > TR = Trigger
You can get the full list of options via:
SELECT name
FROM master..spt_values
WHERE type = 'O9T'
Solution 5 - Sql
As of SQL SERVER 2016 you can use the new DROP PROCEDURE IF EXISTS
.
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]
Reference : https://msdn.microsoft.com/en-us/library/ms174969.aspx
Solution 6 - Sql
I know it is a very old post, but since this appears in the top search results hence adding the latest update for those using SQL Server 2016 SP1 -
create or alter procedure procTest
as
begin
print (1)
end;
go
This creates a Stored Procedure if does not already exist, but alters it if exists.
Solution 7 - Sql
DROP IF EXISTS is a new feature of SQL Server 2016
DROP PROCEDURE IF EXISTS dbo.[procname]
Solution 8 - Sql
I had the same error. I know this thread is pretty much dead already but I want to set another option besides "anonymous procedure".
I solved it like this:
-
Check if the stored procedure exist:
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='my_procedure') BEGIN print 'exists' -- or watever you want END ELSE BEGIN print 'doesn''texists' -- or watever you want END
-
However the
"CREATE/ALTER PROCEDURE' must be the first statement in a query batch"
is still there. I solved it like this:SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE -- view procedure function or anything you want ...
-
I end up with this code:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('my_procedure')) BEGIN DROP PROCEDURE my_procedure END SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].my_procedure ...
Solution 9 - Sql
Here's a method and some reasoning behind using it this way. It isn't as pretty to edit the stored proc but there are pros and cons...
UPDATE: You can also wrap this entire call in a TRANSACTION. Including many stored procedures in a single transaction which can all commit or all rollback. Another advantage of wrapping in a transaction is the stored procedure always exists for other SQL connections as long as they do not use the READ UNCOMMITTED transaction isolation level!
-
To avoid alters just as a process decision. Our processes are to always IF EXISTS DROP THEN CREATE. If you do the same pattern of assuming the new PROC is the desired proc, catering for alters is a bit harder because you would have an IF EXISTS ALTER ELSE CREATE.
-
You have to put CREATE/ALTER as the first call in a batch so you can't wrap a sequence of procedure updates in a transaction outside dynamic SQL. Basically if you want to run a whole stack of procedure updates or roll them all back without restoring a DB backup, this is a way to do everything in a single batch.
IF NOT EXISTS (select ss.name as SchemaName, sp.name as StoredProc from sys.procedures sp join sys.schemas ss on sp.schema_id = ss.schema_id where ss.name = 'dbo' and sp.name = 'MyStoredProc') BEGIN DECLARE @sql NVARCHAR(MAX)
-- Not so aesthetically pleasing part. The actual proc definition is stored -- in our variable and then executed. SELECT @sql = 'CREATE PROCEDURE [dbo].[MyStoredProc]
( @MyParam int ) AS SELECT @MyParam' EXEC sp_executesql @sql END
Solution 10 - Sql
In Sql server 2008 onwards, you can use "INFORMATION_SCHEMA.ROUTINES
"
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MySP'
AND ROUTINE_TYPE = 'PROCEDURE')
Solution 11 - Sql
**The simplest way to drop and recreate a stored proc in T-Sql is **
Use DatabaseName
go
If Object_Id('schema.storedprocname') is not null
begin
drop procedure schema.storedprocname
end
go
create procedure schema.storedprocname
as
begin
end
Solution 12 - Sql
Here is the script that I use. With it, I avoid unnecessarily dropping and recreating the stored procs.
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspMyProcedure]')
)
BEGIN
EXEC sp_executesql N'CREATE PROCEDURE [dbo].[uspMyProcedure] AS select 1'
END
GO
ALTER PROCEDURE [dbo].[uspMyProcedure]
@variable1 INTEGER
AS
BEGIN
-- Stored procedure logic
END
Solution 13 - Sql
why don't you go the simple way like
IF EXISTS(SELECT * FROM sys.procedures WHERE NAME LIKE 'uspBlackListGetAll')
BEGIN
DROP PROCEDURE uspBlackListGetAll
END
GO
CREATE Procedure uspBlackListGetAll
..........
Solution 14 - Sql
In addition to the answer from @Geoff I've created a simple tool which generates a SQL-file which statements for Stored Procedures, Views, Functions and Triggers.
See MyDbUtils @ CodePlex.
Solution 15 - Sql
I wonder! Why i don't write the whole query like
GO
create procedure [dbo].[spAddNewClass] @ClassName varchar(20),@ClassFee int
as
begin
insert into tblClass values (@ClassName,@ClassFee)
end
GO
create procedure [dbo].[spAddNewSection] @SectionName varchar(20),@ClassID int
as
begin
insert into tblSection values(@SectionName,@ClassID)
end
Go
create procedure test
as
begin
select * from tblstudent
end
i already know that first two procedures are already exist sql will run the query will give the error of first two procedures but still it will create the last procedure SQl is itself taking care of what is already exist this is what i always do to all my clients!
Solution 16 - Sql
CREATE Procedure IF NOT EXISTS 'Your proc-name' () BEGIN ... END