Should I put the error handler inside the loop, or the loop inside the error handler?

Question

When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run as quickly as possible.

I'm developing a maintenance job that deletes expired data from a table in chunks. It's designed to run continuously. It's implemented by two stored procedures: the first deletes from the table, and the second calls the other in a loop until either there is no expired data left to delete, or an error occurs.

The delete procedure looks like this:

CREATE PROCEDURE DeleteSomeExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT,
  @ActualDeleteCount INT OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON;

  DELETE TOP (@MaxDeleteCount)
  FROM BigTable
  WHERE DataCollectionDate < DATEADD(DAY, -@ExpiryAgeInDays, GETDATE());

  SET @ActualDeleteCount = @@ROWCOUNT;
END;

I want the looping procedure to reraise errors from the delete procedure, so I'm combining a TRY...CATCH statement and a WHILE BEGIN...END statement.

I can either put the WHILE BEGIN...END loop inside the TRY...CATCH statement:

CREATE PROCEDURE CycleDeleteExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ActualDeleteCount INT = 1;

  BEGIN TRY
    WHILE @ActualDeleteCount > 0
    BEGIN
      EXEC DeleteSomeExpiredData
        @ExpiryAgeInDays = @ExpiryAgeInDays,
        @MaxDeleteCount = @MaxDeleteCount,
        @ActualDeleteCount = @ActualDeleteCount OUTPUT;
    END;
  END TRY
  BEGIN CATCH
    EXEC RethrowError;
  END CATCH;
END;

Or I can put the TRY...CATCH statement inside the WHILE BEGIN...END statement:

CREATE PROCEDURE CycleDeleteExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ActualDeleteCount INT = 1;

  WHILE @ActualDeleteCount > 0
  BEGIN
    BEGIN TRY
      EXEC DeleteSomeExpiredData
        @ExpiryAgeInDays = @ExpiryAgeInDays,
        @MaxDeleteCount = @MaxDeleteCount,
        @ActualDeleteCount = @ActualDeleteCount OUTPUT;
    END TRY
    BEGIN CATCH
      EXEC RethrowError;
    END CATCH;
  END;
END;

These look functionally equivalent, and I find both easy to read. But there may be a semantic difference I'm not aware of. Can you tell me any?

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

Error handling should be (IMO) for the whole stored procedure: so loop inside the TRY/CATCH

Not least, what happens when more code is added pre- or post-loop?
Then you need the TRY/CATCH to cover both loop and new code.

Poor maintainability and extra work required.

You can have an inner TRY/CATCH if you want to handle certain inner errors: this is acceptable for, say, "UPSERT" calls (before SQL Server 2008 MERGE of course). See Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting for more

score 0 · Answer 2 · answered Mar 02 '12 at 15:07

I think you need to assess the requirement. If your goals are to continue on error you may need to further your understanding of why to put a try/catch inside the loop vs outside the loop.

In general if your intent is to simply raise the error from the catch block and conduct no other processing, it is probably unnecessary all together. Here is some sample code to illustrate the difference in processing outcomes when an error is encountered.

DECLARE @i INT = 1;

BEGIN TRY
    WHILE @i <= 5
    BEGIN
        BEGIN TRY
            IF @i = 3
            BEGIN
                RAISERROR (
                        'raised an error',
                        16,
                        1
                        )
            END
            ELSE
                PRINT 'continue ' + Cast(@i AS VARCHAR)
        END TRY

        BEGIN CATCH
            PRINT 'inner try catch'
        END CATCH

        SET @i = @i + 1
    END
END TRY

BEGIN CATCH
    PRINT 'Outer Try catch'
END CATCH

SET @i = 1

BEGIN TRY
    WHILE @i <= 5
    BEGIN
        IF @i = 3
        BEGIN
            RAISERROR (
                    'raised an error',
                    16,
                    1
                    )
        END
        ELSE
            PRINT 'continue ' + Cast(@i AS VARCHAR)

        SET @i = @i + 1
    END
END TRY

BEGIN CATCH
    PRINT 'Outer Try catch'
END CATCH

Should I put the error handler inside the loop, or the loop inside the error handler?

2 Answers2