Menu
Sometimes I saw the following code snippet. When is the if @@trancount > 0 necessary with begin try? Both of them? Or it's a safe way(best practice) to check it always in case it's rollback before the check?
begin tran begin try ... just several lines of sql ... if @@trancount > 0 commit tran end try begin catch if @@trancount > 0 rollback tran end catch 
874
@@TRANCOUNT (Transact-SQL)Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback.
Retrieving Error Information In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed: ERROR_NUMBER() returns the number of the error. ERROR_SEVERITY() returns the severity. ERROR_STATE() returns the error state number.
Transcount is a cloud-based logistics management application that enables companies to digitise and automate their logistics business processes.
I can think of a few scenarios to consider when dealing with @@trancount:
I believe Remus Rusanu's Exception handling and nested transactions handles all these possibilities.
117
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
