Should I put the error handler inside the loop, or the loop inside the error handler? 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 &lt; 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 &gt; 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 &gt; 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?
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