Transactions with TRY/CATCH
This post shows the correct way to do transactions in SQL Server complete with TRY/CATCH.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
--do 'stuff'
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK;
END CATCH;
IF @@trancount > 0 ROLLBACK;
The XACT_ABORT option is here to ensure that the entire transaction is handled together, whether that is being rolled back or being committed.
It’s possible that some errors may skip the CATCH block, according to the documention for TRY/CATCH :
TRY…CATCH constructs do not trap the following conditions:
Warnings or informational messages that have a severity of 10 or lower.
Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
Attentions, such as client-interrupt requests or broken client connections.
When the session is ended by a system administrator by using the KILL statement.
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
Compile errors, such as syntax errors, that prevent a batch from running.
Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
Object name resolution errors
The final piece in the puzzle is checking the transaction count to see if any transactions remain, and if there are any then we roll them back.