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