MSSQL 用脚本同时创建多表, 出错时如何全部回滚

[道玄希言]

应用场景:
在原有数据库基础上, 想通过脚本对数据库更新,

如:增加/修改/删除多个表, 和增加/修改/删除多个存储过程

如何让脚本在执行出现错误时, 将之前的更新全部回滚?

即使执行了 SET XACT_ABORT ON 好像也无法回滚.

在事物中间的语句带有 GO 时, 就无法全部回滚. 想请教如何让事物中间带GO的语句,也能执行回滚?
如以下语句:
SET XACT_ABORT ON
BEGIN TRANSACTION
UPDATE test SET name= '1' WHERE id='1'
GO
UPDATE test SET name= '2', WHERE id='1'
GO
UPDATE test SET name= '3' WHERE id='1'
GO
IF @@ERROR > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

[道玄希言]

引用 2 楼 roy_88 的回复:

你加了go 批次 @@ERROR（返回执行的上一个 Transact-SQL 语句的错误号）这个值你获取不到的，

有没有什么办法, 可以实现回滚 ? 比如我需要用脚本添加一个存储过程, 如果我们加上对该存储过程是否存在的判断, 好像不用GO时, 语句又无法执行成功. 比如: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[p_test]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[p_test] GO CREATE PROCEDURE [dbo].[p_test] ... ...

[中国风]

改为这样用

IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[p_test]') 
    AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[p_test]
EXEC (N'CREATE PROCEDURE [dbo].[p_test]
AS
SELECT 1
')

[中国风]

用法错误 用了SET XACT_ABORT ON 不需要指定回滚

[中国风]

你加了go 批次 @@ERROR（返回执行的上一个 Transact-SQL 语句的错误号）这个值你获取不到的，

[道玄希言]

引用 4 楼 roy_88 的回复:

改为这样用

IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[p_test]') 
    AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[p_test]
EXEC (N'CREATE PROCEDURE [dbo].[p_test]
AS
SELECT 1
')

谢版主, 这样用确实可行, 谢谢了!