在 sql server 上运行条件 DDL 语句 [英] running conditional DDL statements on sql server

问题描述

我有一种情况，我想检查某个列(如版本号)，然后应用一堆 ddl 更改

i have a situation where i want to check a certain column ( like version number) and then apply a bunch of ddl changes

问题是我无法在 IF BEGIN END 块中执行此操作，因为 DDL 语句需要在它们之间使用 GO 分隔符，而 TSQL 不允许这样做.

trouble is i am not able to do it with in a IF BEGIN END block, since DDL statements require a GO separator between them, and TSQL wont allow that.

我想知道是否有任何方法可以做到这一点

I am wondering if there is any way around to accomplish this

推荐答案

您不需要使用完整的块.如果您不使用 BEGIN/END —— 包括单个 DDL 语句，则条件将完整地执行下一条语句.这相当于 Pascal、C 等中 if 的行为.当然，这意味着您将不得不一遍又一遍地重新检查您的条件.这也意味着使用变量来控制脚本的行为几乎是不可能的.

You don't need to use a full block. A conditional will execute the next statement in its entirety if you don't use a BEGIN/END -- including a single DDL statement. This is equivalent to the behavior of if in Pascal, C, etc. Of course, that means that you will have to re-check your condition over and over and over. It also means that using variables to control the script's behavior is pretty much out of the question.

If ((SELECT Version FROM table WHERE... ) <= 15)
CREATE TABLE dbo.MNP (
....
)
GO

If ((SELECT Version FROM table WHERE... ) <= 15)
ALTER TABLE dbo.T1
ALTER COLUMN Field1 AS CHAR(15)
GO

...

或者类似的东西，取决于你的情况.

Or something like that, depending on what your condition is.

不幸的是，CREATE/ALTER PROCEDURE 和 CREATE/ALTER VIEW 有特殊要求，这使得使用起来更加困难.它们几乎必须是语句中的唯一内容，因此您根本无法将它们与 IF 结合起来.

Unfortunately, CREATE/ALTER PROCEDURE and CREATE/ALTER VIEW have special requirements that make it much harder to work with. They are pretty much required to be the only thing in a statement, so you can't combine them with IF at all.

对于许多场景，当您想要升级"您的对象时，您可以将其作为条件删除然后创建:

For many scenarios, when you want to "upgrade" your objects, you can work it as a conditional drop followed by a create:

IF(EXISTS(SELECT * FROM sys.objects WHERE type='p' AND object_id = OBJECT_ID('dbo.abc')))
DROP PROCEDURE dbo.abc
GO

CREATE PROCEDURE dbo.abc
AS
    ...
GO

如果您确实需要条件逻辑来决定做什么，那么我所知道的唯一方法是使用 EXECUTE 将 DDL 语句作为字符串运行.

If you do really need conditional logic to decide what to do, then the only way I know of is to use EXECUTE to run the DDL statements as a string.

If ((SELECT Version FROM table WHERE... ) <= 15)
EXECUTE 'CREATE PROC dbo.abc 
AS
    ....
')

但这很痛苦.您必须对程序主体中的任何引号进行转义，这真的很难阅读.

But this is very painful. You have to escape any quotes in the body of the procedure and it's really hard to read.

根据您需要应用的更改，您会发现这一切可能会变得非常难看.以上甚至不包括错误检查，这本身就是一种皇家痛苦.这就是为什么成群结队的工具制造商通过想办法自动创建部署脚本来谋生.

Depending on the changes that you need to apply, you can see all this can get very ugly fast. The above doesn't even include error checking, which is a royal pain all on its own. This is why hordes of toolmakers make a living by figuring out ways to automate the creation of deployment scripts.

对不起；没有简单的正确"方式适用于所有情况.这只是 TSQL 支持很差的东西.不过，以上应该是一个好的开始.

Sorry; there is no easy "right" way that works for everything. This is just something that TSQL supports very poorly. Still, the above should be a good start.

这篇关于在 sql server 上运行条件 DDL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！