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

user_4525 0
Viewed 96772

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.

tsql sql-server sql
14 Answers

I always alter my objects because a drop is really bad practice and can leave your DB in an bad state if an object fails to create (24/7 db!), as well as what the other posters have mentioned about nuking permissions.

Editors like Sublime, Atom, and VS Code will let you make code snippets as templates for these to quickly gen-up your skeleton script. SQL 2016 now finally supports DROP IF EXISTS construct, but it still approaches from the wrong direction - that everything is a drop/create instead of a one time create in the distant past and alter from then on. Also, I have tried to make my headers as short as will possibly work, so I don't get any fancier than create proc dbo.myproc as as the create stub.

Views:

if objectproperty(object_id('dbo.myview'), 'IsView') is null begin
    exec('create view dbo.myview as select 1 c')
end
go
alter view dbo.myview as
    -- select *
    -- from table
go

Procs:

if objectproperty(object_id('dbo.myproc'), 'IsProcedure') is null begin
    exec('create proc dbo.myproc as')
end
go
alter procedure dbo.myproc as
    set nocount on
    -- Add the stored proc contents here...
go

UDF (scalar):

if objectproperty(object_id('dbo.myudf'), 'IsScalarFunction') is null begin
    exec('create function dbo.myudf returns int as begin return null end')
end
go
alter function dbo.myudf(@s varchar(100)) returns int as
begin
    -- return len(@s)
end
go

UDF (tabular):

if objectproperty(object_id('dbo.myudf'), 'IsTableFunction') is null begin
    exec('create function dbo.myudf returns @t table(x int) as begin return end')
end
go
alter function dbo.myudf(@s varchar(100))
    returns @result table (
        -- Columns returned by the function
        id int identity(1, 1) primary key not null
        ,result varchar(100) null
    )
begin
    return
end
go
user_831440
Related