你在 SQL Server 中做什么来创建或更改? [英] What do you do in SQL Server to CREATE OR ALTER?

问题描述

年份是 2009 年，SQL Server 没有 CREATE OR ALTER/REPLACE.这就是我所做的.

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?

正如 n8wrl 所建议的，官方说法表明此功能不是高优先级.因此问题来了.

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

推荐答案

这篇文章很好地说明了在 SQL Server 中删除对象时丢失权限的问题.

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

• Tips 'N' Tricks – T-SQL – 一种创建或更改存储过程的优雅方式- 易于维护

所以这里是保留权限的方法:

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

也适用于函数，只需将上面代码中的 PROCEDURE 替换为 FUNCTION 即可.

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

考虑这样做的另一个原因是对失败的容忍度.假设您的 DROP 成功，但您的 CREATE 失败 - 您以损坏的数据库结束.使用 ALTER 方法，您最终会得到对象的旧版本.

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.

这篇关于你在 SQL Server 中做什么来创建或更改?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！