create stored procedure if doesn't exist in sql server

Question

Oracle does "create or replace" statements. Sql server does not seem to - if you are scripting out from Enterprise Manager, it instead suggests "drop and create" instead. Drop and create is undesirable in any situation where you've done grants on the stored procedure, because it tosses out any grants your database administration team has done. You really need "create or replace" to help with separation of conerns between developers and administrators.

What I've been doing recently is this:

use [myDatabase]
go

create procedure myProcedure as
begin
  print 'placeholder'
end
go

alter procedure myProcedure as
begin
  -- real sproc code here
end
go

This does what I want. If the procedure doesn't exist, create it then alter in the correct code. If the procedure does exist, the create fails and the alter updates the code with the new code.

It creates a different problem for the administrators, because the create throws a misleading error if the stored procedure already exists. Misleading, of course, in the fact that you shouldn't see red error text when the desired outcome has occured.

Does anyone have a way to suppress the red text? Everything I've tried leads to a 'CREATE/ALTER PROCEDURE must be the first statement in a query batch' error in some way or another.

Possible duplicate of [What do you do in SQL Server to CREATE OR ALTER?](http://stackoverflow.com/questions/1434160/what-do-you-do-in-sql-server-to-create-or-alter) — ruffin, Mar 08 '16 at 15:22
I am going to point out that if you script the permissions as part of your proc then there is no problem in Drop and Create.If you have special permission on a proc, then it should be part of the script that is stored in the source code repository. Then when you go to make a change, there permissionare already there. — HLGEM, Feb 22 '17 at 19:05

score 51 · Accepted Answer · edited Dec 15 '14 at 19:09

51

This will work and keep the permissions intact:

use [myDatabase]
go
if object_id('dbo.myProcedure', 'p') is null
    exec ('create procedure myProcedure as select 1')
go
alter procedure myProcedure as
SET NOCOUNT ON
  -- real sproc code here. you don't really need BEGIN-END
go

edited Dec 15 '14 at 19:09

Nam G VU

33,193
69
233
372

answered Dec 13 '12 at 18:37

RichardTheKiwi

105,798
26
196
262

This works with user-defined-functions as well, which is where I found the need for "alter" because the function was being used by several things. To the OP, this would be preferred option, IMHO. – granadaCoder Dec 15 '14 at 19:38
Perfect answer for people who want to keep DB operations self-contained. – displayName Nov 30 '16 at 15:40
what is procedure has some inputs? Where shall we define them – Priyank Doshi May 12 '20 at 10:25

RBarryYoung · Answer 2 · 2012-12-14T17:21:49.480

10

Like this:

IF  NOT EXISTS (SELECT * FROM sys.objects 
                WHERE object_id = OBJECT_ID(N'[dbo].[myProcedure]') 
                  AND type in (N'P', N'PC'))
BEGIN
    EXEC('
        create procedure myProcedure as
        begin
            print ''placeholder''
        end
        ')
END

EXEC('
    alter procedure myProcedure as
    begin
      -- real sproc code here
    end
    ')

NOTES:

remember to double up your quotes in the dynamic SQL strings.
I have indented it for readability, but that will also add the extra indent spaces to your actual procedures listings. If you don't wnat that, then just reduce the indentation level on the dynamic SQL text.

edited Dec 14 '12 at 17:21

answered Dec 13 '12 at 18:35

RBarryYoung

55,398
14
96
137

FYI, I try to always add in the BEGIN..ENDs when posting online. Just because of how many times I've seen a novice shoot themselves in the foot when they try to add a line to someone else's SQL but forget to add the BEGIN.END. – RBarryYoung Dec 13 '12 at 18:45
Extraneous, really. The "GO" on a new line completely scopes it. What's to say someone won't do `begin...end` and put more statements after `END` but before `GO`? But it's just an opinion and everyone's entitled to one! – RichardTheKiwi Dec 13 '12 at 18:51
@RichardTheKiwi: That's an uncommon mistake. (Well, misunderstanding GO is a common mistake, but it usually manifests differently, as variables out of scope) Usually, the problem that I see with no BEGIN..END is that they will forget that they are necessary for multiple statements in a branch/block. When the BEGIN..END is there, it seems to visually "remind" them, so they won't put the new line outside of the block. (at least I've never seen that mistake) – RBarryYoung Dec 13 '12 at 18:59
@RichardTheKiwi: FWIW, I was just partly explaining my code's verbosity. When I'm writing SQL for my own use it looks a lot sparser. – RBarryYoung Dec 13 '12 at 19:09
The ELSE clause is going to guarantee that you end up with the placeholder instead of the real code if the stored procedure doesn't exist. – quillbreaker Dec 14 '12 at 17:18

score 8 · Answer 3 · answered Feb 22 '17 at 18:59

Finally the day is here where SQL Server has implemented an equivalent to Create or Replace. Their equivalent is "Create or Alter". This is available as of SQL Server 2016 SP1. Example usage:

use [myDatabase]
go

Create or Alter procedure myProcedure as
begin
  -- procedure code here
end
go

create stored procedure if doesn't exist in sql server

3 Answers3