CODEWITHSUNDEEP
sqlsql-servert-sql
quillbreaker
quillbreaker

Reputation: 6215

create stored procedure if doesn't exist in sql server

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.

Upvotes: 22

Views: 46599

Answers (3)

danjuggler
danjuggler

Reputation: 1320

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

Upvotes: 9

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

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

Upvotes: 50

RBarryYoung
RBarryYoung

Reputation: 56755

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:

  1. remember to double up your quotes in the dynamic SQL strings.
  2. 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.

Upvotes: 10

Related Questions