Use XACT_STATE() To Check For Doomed Transactions

Let's say you have the following transaction

BEGIN TRANSACTION TranA
BEGIN TRY
DECLARE @cond INT;
SET @cond = 1
END TRY
BEGIN CATCH
PRINT 'a'
END CATCH;
COMMIT TRAN TranA

This first transaction runs without a problem


Now change value of @cond from 1 to 'A' and run this code below.
BEGIN TRANSACTION TranA
BEGIN TRY
DECLARE @cond INT;
SET @cond = 'A';
END TRY
BEGIN CATCH
PRINT 'a'
END CATCH;
COMMIT TRAN TranA



This transaction will blow up with the following message
Server: Msg 3930, Level 16, State 1, Line 15
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Server: Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.


We can use XACT_STATE() to check if we need to rollback or not without blowing up. I also used the ERROR_MESSAGE() function to print the error

BEGIN TRANSACTION TranA
BEGIN TRY
DECLARE @cond INT;
SET @cond = 'A';
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH;
IF XACT_STATE() =0
BEGIN
COMMIT TRAN TranA
END
ELSE
BEGIN
ROLLBACK TRAN TranA
END

After running that we can see that the following message was printed

Conversion failed when converting the varchar value 'A' to data type int.

We trapped the error, rolled back the transaction and the transaction did not blow up