SQL Server 2008 R2 Transaction 是必需的@@error 并且是 ROLLBACK TRANS 必需的

Question

我的同事在程序中有这个:

BEGIN TRAN
 --Some deletes and inserts
IF(@@error <> 0)
BEGIN
  ROLLBACK TRAN
  RETURN
END
COMMIT TRAN

我在存储过程中有另一个简单的是:

BEGIN TRANSACTION
  --Some deltes and inserts
COMMIT TRANSACTION

我已经测试并发现我的程序总是在错误期间回滚所有内容(测试例如更改列数据类型等)而没有明确编码回滚。我还读到使用 @@error 条件对于 SQL Server 2005 及更高版本已过时。

您认为对 SQL Server 2008 R2 及更高版本进行事务处理的正确方法是什么？谢谢

Answer 1

YES，ROLLBACK 是必需的!

我会基于此模板为 SQL Server 2005 和更新版本创建一个存储过程:

BEGIN TRANSACTION
BEGIN TRY

    -- put your T-SQL commands here    

    -- if successful - COMMIT the work
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- handle the error case (here by displaying the error)
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    -- in case of an error, ROLLBACK the transaction    
    ROLLBACK TRANSACTION

    -- if you want to log this error info into an error table - do it here 
    -- *AFTER* the ROLLBACK
END CATCH