Stored Procedure Error Handling Best Practices, Part 3

In part 1 of this series, I outlined the design of a solution that I have used to record errors in a manner that allows a unique ID to be returned to the user which in turn can be used to trace the error.

In part 2 I described the database objects that are required to implement this solution.

In this part of the series I will explain how to implement this solution within stored procedures.

To start with, there are two types of implementation:

  • Stored procedures that manage transactions
  • Stored procedures that are executed within transactions

As a rule, any procedure that is to be called by an external application should have manage its own transactions.  I suffix this type of procedure with _trn to allow easy identification.

Within a nested procedure

The aim of this solution, when implemented within a nested procedure call is to be able to record any error that occurs within the procedure, or within any further nested procedure calls and then return to the calling procedure a record of the errors.

To implement this, the first step is to provide the procedure with an output parameter that will accept and return an xml variable.

1 CREATE PROCEDURE dbo.Error_SQLTest ( 2 @aSPMode INT = 0 3 , @aErrorLog XML OUTPUT 4 ) AS

As a best practice I always use @aErrorLog as the name of the parameter and always list it last in the parameter list.

The next step is to define the local parameters that will be required:

1 /************************************************** 2 Declare default variables 3 **************************************************/ 4 DECLARE 5 @CalledSPReturnValue INT = 0 6 , @CodeBlockDesc NVARCHAR(256) = '' 7 , @ErrorSQLCategory NVARCHAR(32) = '' 8 , @ReturnValue INT = 0 9 , @Rowcount INT = 0 10 , @SPName NVARCHAR(128) = 'dbo.Error_SQLTest' 11 ;

The default variables are defined for all nested stored procedures – again using the same names as a best practice.

The simplest example of a code block is as follows:

1 /**************************************************/ 2 SET @CodeBlockDesc = 'Validate input parameters' 3 /**************************************************/ 4 BEGIN TRY 5 IF @aSPMode IS NULL 6 BEGIN 7 SET @aSPMode = 0; 8 END 9 END TRY 10 BEGIN CATCH 11 SET @ReturnValue = -1; 12 SET @ErrorSQLCategory = 'Fatal'; 13 GOTO ErrorHandler; 14 END CATCH

Line 2 shows the description of the code block being set – this will be logged as part of the error if an error occurs.

The catch block allows the return value and the SQL error category to be set.  Again these will be logged as part of the error and the return value will be returned to the calling procedure.

If the code block were selecting from, inserting into, deleting from or updating a table the following syntax can be used:

1 /**************************************************/ 2 SET @CodeBlockDesc = 'Select from dbo.Error' 3 /**************************************************/ 4 BEGIN TRY 5 INSERT INTO dbo.Error ( 6 ErrorID 7 ) VALUES ( 8 1 9 ) 10 ; 11 12 IF @@rowcount = 0 13 BEGIN 14 SET @ReturnValue = 1; 15 SET @ErrorSQLCategory = 'No rows affected'; 16 GOTO ErrorHandler; 17 END 18 END TRY 19 BEGIN CATCH 20 SET @ReturnValue = -1; 21 SET @ErrorSQLCategory = 'Fatal'; 22 GOTO ErrorHandler; 23 END CATCH

In a similar manner, stored procedures can be called and the output recorded:

1 /**************************************************/ 2 SET @CodeBlockDesc = 'Call dbo.Error_SQLTest' 3 /**************************************************/ 4 BEGIN TRY 5 EXEC @CalledSPReturnValue = dbo.Error_SQLTest 6 @aSPMode = @aSPMode 7 , @aErrorLog = @aErrorLog OUTPUT 8 ; 9 10 IF @CalledSPReturnValue != 0 11 BEGIN 12 SELECT 13 @ReturnValue = @CalledSPReturnValue 14 , @ErrorSQLCategory = CASE 15 WHEN @CalledSPReturnValue < 0 THEN 'Hard Error' 16 WHEN @CalledSPReturnValue > 0 THEN 'Soft Error' 17 ELSE 'Unknown' 18 END 19 ; 20 GOTO ErrorHandler; 21 END 22 END TRY 23 BEGIN CATCH 24 SET @ReturnValue = -1; 25 SET @ErrorSQLCategory = 'Fatal'; 26 GOTO ErrorHandler; 27 END CATCH

In this example the return value of the stored procedure is evaluated to establish the category of the error.

All of these examples end with a GOTO statement that goes to the ErrorHandler section of the stored procedure.

The ErrorHander section of the procedure is preceded by the Success section.

1 /**************************************************/ 2 Success: 3 /**************************************************/ 4 RETURN @ReturnValue; 5 6 /**************************************************/ 7 ErrorHandler: 8 /**************************************************/ 9 10 /**************************************************/ 11 SET @CodeBlockDesc = 'Handle error' 12 /**************************************************/ 13 BEGIN TRY 14 EXEC @CalledSPReturnValue = dbo.Error_SQLHandler 15 @aSPMode = 0 16 , @aErrorSQLCodeblock = @CodeBlockDesc 17 , @aErrorSQLCategory = @ErrorSQLCategory 18 , @aErrorSQLProcedure = @SPName 19 , @aErrorLog = @aErrorLog OUTPUT 20 ; 21 22 IF @CalledSPReturnValue != 0 23 BEGIN 24 SELECT 25 @ReturnValue = @CalledSPReturnValue 26 , @ErrorSQLCategory = CASE 27 WHEN @CalledSPReturnValue < 0 THEN 'Hard Error' 28 WHEN @CalledSPReturnValue > 0 THEN 'Soft Error' 29 ELSE 'No Error' 30 END 31 ; 32 GOTO ErrorHandlerFinal; 33 END 34 END TRY 35 BEGIN CATCH 36 SET @ReturnValue = -1; 37 SET @ErrorSQLCategory = 'Fatal'; 38 GOTO ErrorHandlerFinal; 39 END CATCH 40 41 RETURN @ReturnValue; 42 43 /**************************************************/ 44 ErrorHandlerFinal: 45 /**************************************************/ 46 EXEC @CalledSPReturnValue = dbo.Error_RaiseError 47 @aSPMode = @aSPMode 48 , @aErrorSQLCodeblock = @CodeBlockDesc 49 , @aErrorSQLCategory = @ErrorSQLCategory 50 , @aErrorSQLProcedure = @SPName 51 ; 52 53 RETURN @ReturnValue;

Line 14 shows the call to Error_SQLHandler which will add the error to the @aErrorLog xml variable.  If there is an error with this process then the ErrorHandlerFinal code block will be executed which will call Error_RaiseError.

For consistency all of the code in the Success, ErrorHandler and ErrorHandlerFinal code blocks should be identical in all nested procedures.

With in a transaction controlling procedure

These procedures aim to identify the existence of an error via a return value and identify the exact error by means of an output parameter called @aErrorID.

1 CREATE PROCEDURE dbo.Error_SQLTest_trn ( 2 @aSPMode INT = 0 3 , @aErrorID INT OUTPUT 4 ) AS

The xml variable to hold the errors is defined under local variables.

1 /************************************************** 2 Declare local variables 3 **************************************************/ 4 DECLARE 5 @ErrorLog XML 6 ;

All of the error handling within the code blocks is then the same as it is in the nested procedure calls.  The major difference is encountered in the Success and ErrorHandler code blocks:

1 /**************************************************/ 2 Success: 3 /**************************************************/ 4 IF XACT_STATE() = -1 5 BEGIN 6 GOTO ErrorHandler; 7 END 8 9 IF XACT_STATE() = 1 10 BEGIN 11 COMMIT TRAN; 12 END 13 14 RETURN @ReturnValue;

The Success code block checks whether the transaction is committable and if it is not redirects to the the ErrorHandler, otherwise, if necessary, it commits the transaction.  The return value is then returned to the application that called the procedure.

The ErrorHandler  code block is identical but it is followed by the committing of or rolling back of the transaction.

1 /**************************************************/ 2 ErrorHandler: 3 /**************************************************/ 4 5 /**************************************************/ 6 SET @CodeBlockDesc = 'Handle error' 7 /**************************************************/ 8 BEGIN TRY 9 EXEC @CalledSPReturnValue = dbo.Error_SQLHandler 10 @aSPMode = 0 11 , @aErrorSQLCodeblock = @CodeBlockDesc 12 , @aErrorSQLCategory = @ErrorSQLCategory 13 , @aErrorSQLProcedure = @SPName 14 , @aErrorLog = @ErrorLog OUTPUT 15 ; 16 17 IF @CalledSPReturnValue != 0 18 BEGIN 19 SELECT 20 @ReturnValue = @CalledSPReturnValue 21 , @ErrorSQLCategory = CASE 22 WHEN @CalledSPReturnValue < 0 THEN 'Hard Error' 23 WHEN @CalledSPReturnValue > 0 THEN 'Soft Error' 24 ELSE 'No Error' 25 END 26 ; 27 GOTO ErrorHandlerFinal; 28 END 29 END TRY 30 BEGIN CATCH 31 SET @ReturnValue = -1; 32 SET @ErrorSQLCategory = 'Fatal'; 33 GOTO ErrorHandlerFinal; 34 END CATCH 35 36 IF XACT_STATE() = 1 AND @ReturnValue >= 0 37 BEGIN 38 COMMIT TRAN; 39 END 40 41 IF XACT_STATE() != 0 42 BEGIN 43 ROLLBACK TRAN; 44 END

The next step, following the possible roll back of the transaction is to commit the errors held in the @ErrorLog variable to the database:

1 /**************************************************/ 2 SET @CodeBlockDesc = 'Commit error' 3 /**************************************************/ 4 BEGIN TRY 5 EXEC @CalledSPReturnValue = dbo.Error_SQLCommit 6 @aSPMode = 0 7 , @aErrorLog = @ErrorLog 8 , @aErrorID = @aErrorID OUTPUT 9 ; 10 11 IF @CalledSPReturnValue != 0 12 BEGIN 13 SELECT 14 @ReturnValue = @CalledSPReturnValue 15 , @ErrorSQLCategory = CASE 16 WHEN @CalledSPReturnValue < 0 THEN 'Hard Error' 17 WHEN @CalledSPReturnValue > 0 THEN 'Soft Error' 18 ELSE 'No Error' 19 END 20 ; 21 GOTO ErrorHandlerFinal; 22 END 23 END TRY 24 BEGIN CATCH 25 SET @ReturnValue = -1; 26 SET @ErrorSQLCategory = 'Fatal'; 27 GOTO ErrorHandlerFinal; 28 END CATCH 29 30 RETURN @ReturnValue;

If this process was successful then the execution of the procedure is terminated by the return of the return value.  @aErrorID will have already been populated as a result of executing Error_SQLCommit.

Again, as in the nested procedure there is an ErrorHandlerFinal code block, in case all else fails:

1 /**************************************************/ 2 ErrorHandlerFinal: 3 /**************************************************/ 4 EXEC @CalledSPReturnValue = dbo.Error_RaiseError 5 @aSPMode = @aSPMode 6 , @aErrorSQLCodeblock = @CodeBlockDesc 7 , @aErrorSQLCategory = @ErrorSQLCategory 8 , @aErrorSQLProcedure = @SPName 9 ; 10 11 RETURN @ReturnValue;

In the next part of the series I will demonstrate the results of this error handling solution.


Scripts are available for the all objects here.

Leave a Reply

Your email address will not be published.