Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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

share|improve this answer

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
share|improve this answer
    
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. –  Iain Elder Mar 2 '12 at 15:37
    
I believe you will want the try/catch on the outside of the while loop in that case. –  doug_w Mar 2 '12 at 15:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.