set xact_abort on and try catch together

Question

i have a try catch block in my sp with just a insert statement in the try. the catch check error code if it is pk violation, if it is then do update. but some times i get "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back." so i added xact_abort on, but then i keep getting "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements." and i found this. http://www.ashishsheth.com/post/2009/08/14/Set-XACT_ABORT-ON-and-TryCatch-block-in-Sql-Server-2005.aspx

if this true. will my catch code not run if there is a error in my try block with xact_abort on?

Answer 1

It is not true, at least with SQL SERVER 2008, that SET XACT_ABORT ON will cause an error to skip the CATCH block:

Here is the code I tried using the Northwind database

SET XACT_ABORT OFF
BEGIN TRY
    SELECT 1,  @@TRANCOUNT
BEGIN TRAN
    UPDATE [dbo].[Categories]
    SET Description='BLAH'
    WHERE [CategoryID]=2
    SELECT 2,  @@TRANCOUNT

    SELECT 1/0 as whoops


COMMIT
    SELECT 3,  @@TRANCOUNT

END TRY
BEGIN CATCH
    SELECT 'In Catch. Error occured', 4,  @@TRANCOUNT

     IF (XACT_STATE()) = 0
    BEGIN
        SELECT
            N'There is no transaction'

    END;


     IF (XACT_STATE()) = -1
    BEGIN
        SELECT
            N'The transaction is in an uncommittable state.' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is committable.
    IF (XACT_STATE()) = 1
    BEGIN
        SELECT
            N'The transaction is committable.' +
            'Committing transaction.'
        COMMIT TRANSACTION;   
    END;

END CATCH

This will, obviously, force an error when it hits the SELECT 1/0 statement. With SET XACT_ABORT OFF, when the CATCH block is reached, the value returned by the XACT_STATE() function is 1, causing the code to run which COMMITs the transaction. When SET XACT_ABORT is on, the value returned, in the CATCH block is -1 so the code which ROLLs back the transaction is executed.

This is based on:

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Answer 2

Let me add that, in that particular scenario (try insert, if PK violation then catch and update), it would be better to use IF EXISTS (select....) to see if the row is there and put your UPDATE statement there. Put your INSERT statement in ELSE block. Much cleaner.