开发者

Handling rollbacks in a SQL Server catch statement

In my reading, I have seen two 开发者_如何学编程methods of handling transaction rollbacks in the CATCH section in a SQL Server stored procedure:

1: if @@trancount > 0 ROLLBACK
2: if XACT_STATE() <> 0 ROLLBACK TRAN

Which is better and why?


This MSDN article is a good reference. Quote:

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.

Say you wanted to potentially COMMIT a transaction in some circumstances, then XACT_STATE will help you here as that will identify whether it's possible to commit or not whereas @@TRANCOUNT couldn't tell you that. If you just want to do a ROLLBACK, either would do the job, so it depends on what you actually need.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜