SQL 如果sql server中不存在则创建存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13866004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
create stored procedure if doesn't exist in sql server
提问by quillbreaker
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.
Oracle 执行“创建或替换”语句。Sql server 似乎没有 - 如果您从企业管理器中编写脚本，它会建议改为“删除并创建”。在您对存储过程进行授权的任何情况下，删除和创建都是不可取的，因为它会丢弃您的数据库管理团队所做的任何授权。您确实需要“创建或替换”来帮助分离开发人员和管理员之间的概念。
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.
有没有人有办法抑制红色文字？我尝试过的一切都会以某种方式导致“创建/更改程序必须是查询批处理中的第一条语句”错误。
回答by RichardTheKiwi
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
回答by RBarryYoung
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:
笔记：
- remember to double up your quotes in the dynamic SQL strings.
- 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.
- 请记住将动态 SQL 字符串中的引号加倍。
- 为了便于阅读，我已将其缩进，但这也会在您的实际过程列表中添加额外的缩进空间。如果您不知道，那么只需减少动态 SQL 文本的缩进级别。
回答by danjuggler
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:
终于有一天，SQL Server 实现了等效于创建或替换的功能。它们的等价物是“创建或更改”。这从 SQL Server 2016 SP1 开始可用。用法示例：
use [myDatabase]
go
Create or Alter procedure myProcedure as
begin
-- procedure code here
end
go
