SQL TSQL - 如何在 BEGIN .. END 块中使用 GO？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6376866/
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
TSQL - How to use GO inside of a BEGIN .. END block?
提问by BlueRaja - Danny Pflughoeft
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 ... ENDstatement.
我正在生成一个脚本，用于将更改从多个开发数据库自动迁移到暂存/生产。基本上，它需要一堆更改脚本，并将它们合并为一个脚本，将每个脚本包装在一个IF whatever BEGIN ... END语句中。
However, some of the scripts require a GOstatement so that, for instance, the SQL parser knows about a new column after it's created.
但是，一些脚本需要一个GO语句，例如，SQL 解析器在创建新列之后就知道它。
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
However, once I wrap that in an IFblock:
但是，一旦我将其包装在一个IF块中：
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
It fails because I am sending a BEGINwith no matching END. However, if I remove the GOit complains again about an unknown column.
它失败了，因为我发送了一个BEGIN没有匹配的END. 但是，如果我删除GO它，它会再次抱怨未知列。
Is there any way to create and update the same column within a single IFblock?
有没有办法在单个IF块中创建和更新同一列？
采纳答案by BlueRaja - Danny Pflughoeft
I ultimately got it to work by replacing every instance of GOon its own line with
我最终通过GO用
END
GO
---Automatic replacement of GO keyword, need to recheck IF conditional:
IF whatever
BEGIN
This is greatly preferable to wrapping every group of statements in a string, but is still farfrom ideal. If anyone finds a better solution, post it and I'll accept it instead.
这比将每组语句包装在一个字符串中更可取，但仍然远非理想。如果有人找到更好的解决方案，请发布它，我会接受它。
回答by Oded
GOis not SQL - it is simply a batch separator used in some MS SQL tools.
GO不是 SQL - 它只是一些 MS SQL 工具中使用的批处理分隔符。
If you don't use that, you need to ensure the statements are executed separately - either in different batches or by using dynamic SQL for the population (thanks @gbn):
如果您不使用它，则需要确保单独执行语句 - 以不同的批次或通过使用动态 SQL 进行填充（感谢 @gbn）：
IF whatever
BEGIN
ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL;
EXEC ('UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever')
END
回答by Mina Jacob
I had the same problem and finally managed to solve it using SET NOEXEC.
我遇到了同样的问题，最后设法使用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;
回答by mellamokb
You could try sp_executesql, splitting the contents between each GOstatement into a separate string to be executed, as demonstrated in the example below. Also, there is a @statementNo variable to track which statement is being executed for easy debugging where an exception occurred. The line numbers will be relative to the beginning of the relevant statement number that caused the error.
您可以尝试sp_executesql将每个GO语句之间的内容拆分为要执行的单独字符串，如下面的示例所示。此外，还有一个@statementNo 变量来跟踪正在执行的语句，以便于在发生异常的地方进行调试。行号将相对于导致错误的相关语句号的开头。
BEGIN TRAN
DECLARE @statementNo INT
BEGIN TRY
IF 1=1
BEGIN
SET @statementNo = 1
EXEC sp_executesql
N' ALTER TABLE dbo.EMPLOYEE
ADD COLUMN EMP_IS_ADMIN BIT NOT NULL'
SET @statementNo = 2
EXEC sp_executesql
N' UPDATE dbo.EMPLOYEE
SET EMP_IS_ADMIN = 1'
SET @statementNo = 3
EXEC sp_executesql
N' UPDATE dbo.EMPLOYEE
SET EMP_IS_ADMIN = 1x'
END
END TRY
BEGIN CATCH
PRINT 'Error occurred on line ' + cast(ERROR_LINE() as varchar(10))
+ ' of ' + 'statement # ' + cast(@statementNo as varchar(10))
+ ': ' + ERROR_MESSAGE()
-- error occurred, so rollback the transaction
ROLLBACK
END CATCH
-- if we were successful, we should still have a transaction, so commit it
IF @@TRANCOUNT > 0
COMMIT
You can also easily execute multi-line statements, as demonstrated in the example above, by simply wrapping them in single quotes ('). Don't forget to escape any single quotes contained inside the string with a double single-quote ('') when generating the scripts.
您还可以轻松地执行多行语句，如上例所示，只需将它们括在单引号 ( ') 中即可。''生成脚本时，不要忘记使用双单引号 ( )转义包含在字符串中的任何单引号。
回答by Andy Joiner
You can enclose the statements in BEGIN and END instead of the GO inbetween
您可以将语句括在 BEGIN 和 END 中，而不是将 GO 括起来
IF COL_LENGTH('Employees','EMP_IS_ADMIN') IS NULL --Column does not exist
BEGIN
BEGIN
ALTER TABLE dbo.Employees ADD EMP_IS_ADMIN BIT
END
BEGIN
UPDATE EMPLOYEES SET EMP_IS_ADMIN = 0
END
END
(Tested on Northwind database)
（在 Northwind 数据库上测试）
Edit: (Probably tested on SQL2012)
编辑：（可能在 SQL2012 上测试过）
回答by Luk
You may try this solution:
你可以试试这个解决方案：
if exists(
SELECT...
)
BEGIN
PRINT 'NOT RUN'
RETURN
END
--if upper code not true
ALTER...
GO
UPDATE...
GO
回答by kavun
I have used RAISERRORin the past for this
我RAISERROR过去曾为此使用过
IF NOT whatever BEGIN
RAISERROR('YOU''RE ALL SET, and sorry for the error!', 20, -1) WITH LOG
END
ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL
GO
UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever
回答by jim a
You can incorporate a GOTOand LABELstatements to skip over code, thus leaving the GOkeywords intact.
您可以合并 a GOTOandLABEL语句来跳过代码，从而使GO关键字保持不变。
