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?

Answer 1

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

Answer 2

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

• Iain Samuel McLean Elder: I want the looping procedure to stop immediately if the inner procedure raises an error. If the looping procedure doesn't explicitly raise the error, the looping procedure does not stop immediately.
• doug_w: I believe you will want the try/catch on the outside of the while loop in that case.