TSQL-如何在 BEGIN. . END 块内使用 GO？

我正在生成一个脚本，用于自动将更改从多个开发数据库迁移到临时/生产环境。基本上，它采用一组变更脚本，并将它们合并到单个脚本中，将每个脚本包装在 IF whatever BEGIN ... END语句中。

但是，有些脚本需要 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

然而，一旦我将其包装在 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

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

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

70021 次浏览
小开

You could try sp_executesql, splitting the contents between each GO statement 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.

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.

小开

GO is not SQL - it is simply a batch separator used in some MS SQL tools.

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):

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
小开

I ultimately got it to work by replacing every instance of GO on its own line with

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 far from ideal. If anyone finds a better solution, post it and I'll accept it instead.

小开

You can incorporate a GOTO and LABEL statements to skip over code, thus leaving the GO keywords intact.

小开

You can enclose the statements in BEGIN and END instead of the GO inbetween

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)

Edit: (Probably tested on SQL2012)

小开
最佳答案

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;
小开

I have used RAISERROR in the past for this

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
小开

You may try this solution:

if exists(
SELECT...
)
BEGIN
PRINT 'NOT RUN'
RETURN
END


--if upper code not true


ALTER...
GO
UPDATE...
GO

提交答案