SQL Server - Try-Catch / xact_abort off and xact_state

427 views Asked by At

We have a data import system where we are processing CSV files, each file contains a set of records that have to be validated and processed individually. If they fail for some reason, they're marked with the error and we then go on to the next record. They can fail for either logical or database errors such as dup key or foreign key errors.

The stored procedure that processes the data is either called from a client application, or from an agent job. The difference is that the app will create a transaction whereas the job will not before calling the proc.

SET NOCOUNT ON;
SET XACT_ABORT OFF;

DECLARE @trancount_in       INT,
        @xstate             INT,
        @Row                INT,
        @MaxRows            INT

SET @trancount_in = @@trancount

-- CODE: import data into temp table
select @Row = 1,
       @MaxRows = @@Rowcount

IF @transcount_in = 0 
    BEGIN TRANSACTION

while @Row <= @MaxRows

    -- CODE: Get data record from table  

    begin try

        save transaction myproc

        -- CODE: validate and process record

    end try

    begin catch

        SELECT @xstate = XACT_STATE()

        IF @xstate = -1 BEGIN
            ROLLBACK
            Raiserror('error',16,1)
        END
        IF @xstate = 1 and @@Trancount = 0 BEGIN
            ROLLBACK
            Raiserror('error',16,1)
        END
        IF @xstate = 1 and @@Trancount > 0
            ROLLBACK TRANSACTION myproc

        -- CODE: update import record with error here

    end catch

    set @Row = @Row + 1
End

IF @transcount_in = 0 
    COMMIT

So fairly simple so far, but looking at lots of documentation, it says that you should check the xact_state for -1 in the catch block, but I don't want the transaction to fail at this point. I just want to go on to the next row in the import. So if xact_abort is off, would we ever get the xact_state of -1 in the catch block?

I know that the catch doesn't actually catch all errors, but I have found out that if you create a parent proc with a try catch block then it will catch more errors than the block in the actual proc. So I will be moving the loop logic to a parent proc and then just do the processing in the current one.

Is there anything else I should be doing to mitigate any errors that might occur?

Hope this isn't too confusing. Cheers Adrian

0

There are 0 answers