What do you do in SQL Server to CREATE OR ALTER?

Question

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES             WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog'               AND ROUTINE_SCHEMA = 'dbo'               AND ROUTINE_TYPE = 'PROCEDURE')  EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')  CREATE PROCEDURE dbo.SynchronizeRemoteCatalog AS BEGIN     -- body END 

For triggers, you have to lean on the proprietary system views.

Is this the most accepted convention in the meantime?

EDIT: As n8wrl suggested, the official word suggests that this feature is not a high priority. Hence the question.

Answer 1

This article makes a good point about losing permissions when dropping an object in SQL server.

• Tips ‘N’ Tricks – T-SQL – An Elegant way to CREATE or ALTER Stored Procedures in One-Go for Easy Maintenance

So here is the approach which retains permissions:

IF OBJECT_ID('spCallSomething') IS NULL     EXEC('CREATE PROCEDURE spCallSomething AS SET NOCOUNT ON;') GO  ALTER PROCEDURE spCallSomething ...  --instead of DROP/CREATE 

Also works for functions, just replace PROCEDURE with FUNCTION in the above code.

Another reason to consider doing it this way is tolerance to failure. Suppose your DROP succeeds, but your CREATE fails - you end with a broken DB. Using ALTER approach, you will end up with an older version of the object.