T-sql 的Transaction模板

T-sql 的Transaction模板

很简单的模板，开始一个Transaction，然后运行一些命令。最后根据情况来判断是否Commit

如果在中间出了严重故障，无法在程序中终止，可以在Management Studio中关闭当前命令窗口，Sqlserver会提示，是否终止未完成的Transaction, 选择No即可。

-- Transaction template
Begin Try
Begin Transaction updateWLReg
Begin
-- leave your code here


End

IF @@ERROR <> 0
BEGIN

print 'rollback transaction because there is a error'

ROLLBACK TRANSACTION

END

ELSE

begin

print 'commiting'

---- change this to Rollback during debuging phase
-- ROLLBACK TRANSACTION
Commit TRANSACTION

end
End Try
Begin Catch
print 'exception happens'
SELECT ERROR_NUMBER() ERNumber,
ERROR_SEVERITY() ErrorSeverity,
ERROR_STATE() ErrorState,
ERROR_PROCEDURE() ErrorProcedure,
ERROR_LINE() ErrorLine,
ERROR_MESSAGE() ErrorMessage

ROLLBACK TRANSACTION
End Catch