Update statements - capture errors and continue with the rest

Viewed 4064

I have around 2000 SQL update commands to run and I know that some of them may fail for various reasons. I'd like to run them all, or in batches, and capture the failures for those that fail, while continuing to go through the rest of the list.

Two approaches that i'm looking at are:

XACT Abort

set xact_abort on
begin transaction
 -- Updates here --
commit transaction

Try catch

BEGIN TRY
    -- Updates here --
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH

The problem with these is that when they fail, they fail (at least, this is what happened in my testing). Then I have to fix the problem with whichever line it fails on and start it again. I want to ignore and log any failures then continue. Is this possible using either xact_abort or a try catch type query or should I be looking at something else?

3 Answers
Related