TSQL - 如何在 BEGIN .. END 块中使用 GO? [英] TSQL - How to use GO inside of a BEGIN .. END block?

问题描述

我正在生成一个脚本，用于将更改从多个开发数据库自动迁移到暂存/生产.基本上，它需要一堆更改脚本，并将它们合并为一个脚本，将每个脚本包装在 IF 任何 BEGIN ... END 语句中.

I am generating a script for automatically migrating changes from multiple development databases to staging/production. Basically, it takes a bunch of change-scripts, and merges them into a single script, wrapping each script in a IF whatever BEGIN ... END statement.

然而，一些脚本需要一个 GO 语句，例如，SQL 解析器在创建新列后知道它.

However, some of the scripts require a GO statement so that, for instance, the SQL parser knows about a new column after it's created.

ALTER TABLE dbo.EMPLOYEE 
ADD COLUMN EMP_IS_ADMIN BIT NOT NULL
GO -- Necessary, or next line will generate "Unknown column:  EMP_IS_ADMIN"
UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever

但是，一旦我将其包装在 IF 块中:

However, once I wrap that in an IF block:

IF whatever
BEGIN
    ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL
    GO
    UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever
END

它失败了，因为我发送了一个 BEGIN 而没有匹配的 END.但是，如果我删除 GO，它会再次抱怨未知列.

It fails because I am sending a BEGIN with no matching END. However, if I remove the GO it complains again about an unknown column.

有没有办法在单个 IF 块中创建和更新同一列?

Is there any way to create and update the same column within a single IF block?

推荐答案

我遇到了同样的问题，最后使用 SET NOEXEC.

I had the same problem and finally managed to solve it using SET NOEXEC.

IF not whatever
BEGIN
    SET NOEXEC ON; 
END

ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL
GO
UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever

SET NOEXEC OFF; 

这篇关于TSQL - 如何在 BEGIN .. END 块中使用 GO?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！