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.

Answer 1

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