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屋!