Stored Procedure Error Handling Best Practices, Part 3

30 Dec 2010 6-minute read Al Eardley
Data Platform
SQL ServerT-SQL

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.

CREATE PROCEDURE dbo.Error_SQLTest (
    @aSPMode         INT    = 0
,   @aErrorLog       XML    OUTPUT
) 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:

**************************************************
Declare default variables
**************************************************/
DECLARE
    @CalledSPReturnValue    INT             = 0
,    @CodeBlockDesc         NVARCHAR(256)   = ''
,    @ErrorSQLCategory      NVARCHAR(32)    = ''
,    @ReturnValue           INT             = 0
,    @Rowcount              INT             = 0
,    @SPName                NVARCHAR(128)   = 'dbo.Error_SQLTest'
;

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:

/**************************************************/
SET @CodeBlockDesc = 'Validate input parameters'
/**************************************************/
BEGIN TRY 5 IF @aSPMode IS NULL
    BEGIN
        SET @aSPMode = 0;
    END
END TRY
BEGIN CATCH
    SET @ReturnValue        = -1;
    SET @ErrorSQLCategory    = 'Fatal';
    GOTO ErrorHandler;
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:

/**************************************************/
SET @CodeBlockDesc = 'Select from dbo.Error'
/**************************************************/
BEGIN TRY
    INSERT INTO dbo.Error (
        ErrorID
    ) VALUES (
        1
    )
    ;

    IF @@rowcount = 0
    BEGIN
        SET @ReturnValue        = 1;
        SET @ErrorSQLCategory    = 'No rows affected';
        GOTO ErrorHandler;
    END
END TRY
BEGIN CATCH
    SET @ReturnValue        = -1;
    SET @ErrorSQLCategory    = 'Fatal';
    GOTO ErrorHandler;
END CATCH

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

/**************************************************/
SET @CodeBlockDesc = 'Call dbo.Error_SQLTest'
/**************************************************/
BEGIN TRY
	EXEC @CalledSPReturnValue = dbo.Error_SQLTest
		@aSPMode                    = @aSPMode
	,    @aErrorLog                    = @aErrorLog OUTPUT
	;

	IF @CalledSPReturnValue != 0
	BEGIN
		SELECT
			@ReturnValue        = @CalledSPReturnValue
		,   @ErrorSQLCategory    = CASE 
				WHEN @CalledSPReturnValue < 0 THEN 'Hard Error'
				WHEN @CalledSPReturnValue \> 0 THEN 'Soft Error'
				ELSE 'Unknown'
			END
		;
		GOTO ErrorHandler;
	END
END TRY
BEGIN CATCH
	SET @ReturnValue        = -1;
	SET @ErrorSQLCategory    = 'Fatal';
	GOTO ErrorHandler;
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.

/**************************************************/
Success:
/**************************************************/
RETURN @ReturnValue;

/**************************************************/
ErrorHandler:
/**************************************************/

/**************************************************/
SET @CodeBlockDesc = 'Handle error'
/**************************************************/
BEGIN TRY
	EXEC @CalledSPReturnValue = dbo.Error_SQLHandler
		@aSPMode                = 0
	,    @aErrorSQLCodeblock        = @CodeBlockDesc
	,    @aErrorSQLCategory        = @ErrorSQLCategory
	,    @aErrorSQLProcedure        = @SPName
	,    @aErrorLog                = @aErrorLog    OUTPUT
	;

	IF @CalledSPReturnValue != 0
	BEGIN
		SELECT
			@ReturnValue        = @CalledSPReturnValue
		,   @ErrorSQLCategory    = CASE 
				WHEN @CalledSPReturnValue < 0 THEN 'Hard Error'
				WHEN @CalledSPReturnValue \> 0 THEN 'Soft Error'
				ELSE 'No Error'
			END
		;

		GOTO ErrorHandlerFinal;
	END
END TRY
BEGIN CATCH
	SET @ReturnValue        = -1;
	SET @ErrorSQLCategory    = 'Fatal';
	GOTO ErrorHandlerFinal;
END CATCH

RETURN @ReturnValue;

/**************************************************/
ErrorHandlerFinal:
/**************************************************/
EXEC @CalledSPReturnValue = dbo.Error_RaiseError
	@aSPMode            = @aSPMode
,    @aErrorSQLCodeblock    = @CodeBlockDesc
,    @aErrorSQLCategory    = @ErrorSQLCategory
,    @aErrorSQLProcedure    = @SPName
;

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.

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

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

/**************************************************
Declare local variables
**************************************************/
DECLARE
	@ErrorLog                XML
;

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:

/**************************************************/
Success:
/**************************************************/
IF XACT_STATE() = -1
BEGIN
	GOTO ErrorHandler;
END

IF XACT_STATE() = 1
BEGIN
	COMMIT TRAN;
END

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.

/**************************************************/
ErrorHandler:
/**************************************************/

/**************************************************/
SET @CodeBlockDesc = 'Handle error'
/**************************************************/
BEGIN TRY
	EXEC @CalledSPReturnValue = dbo.Error_SQLHandler
		@aSPMode                = 0
	,    @aErrorSQLCodeblock        = @CodeBlockDesc
	,    @aErrorSQLCategory        = @ErrorSQLCategory
	,    @aErrorSQLProcedure        = @SPName
	,    @aErrorLog                = @ErrorLog    OUTPUT
	;

	IF @CalledSPReturnValue != 0
	BEGIN
		SELECT
			@ReturnValue        = @CalledSPReturnValue
		,   @ErrorSQLCategory    = CASE 
				WHEN @CalledSPReturnValue < 0 THEN 'Hard Error'
				WHEN @CalledSPReturnValue \> 0 THEN 'Soft Error'
				ELSE 'No Error'
			END
		;
		GOTO ErrorHandlerFinal;
	END
END TRY
BEGIN CATCH
	SET @ReturnValue        = -1;
	SET @ErrorSQLCategory    = 'Fatal';
	GOTO ErrorHandlerFinal;
END CATCH

IF XACT_STATE() = 1 AND @ReturnValue \>= 0
BEGIN
	COMMIT TRAN;
END

IF XACT_STATE() != 0
BEGIN
	ROLLBACK TRAN;
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:

/**************************************************/
SET @CodeBlockDesc = 'Commit error'
/**************************************************/
BEGIN TRY
	EXEC @CalledSPReturnValue = dbo.Error_SQLCommit
		@aSPMode                = 0
	,    @aErrorLog                = @ErrorLog
	,    @aErrorID                = @aErrorID OUTPUT
	;

	IF @CalledSPReturnValue != 0
	BEGIN
		SELECT
			@ReturnValue        = @CalledSPReturnValue
		,   @ErrorSQLCategory    = CASE 
				WHEN @CalledSPReturnValue < 0 THEN 'Hard Error'
				WHEN @CalledSPReturnValue \> 0 THEN 'Soft Error'
				ELSE 'No Error'
			END
		;
		GOTO ErrorHandlerFinal;
	END
END TRY
BEGIN CATCH
	SET @ReturnValue        = -1;
	SET @ErrorSQLCategory    = 'Fatal';
	GOTO ErrorHandlerFinal;
END CATCH

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:

/**************************************************/
ErrorHandlerFinal:
/**************************************************/
EXEC @CalledSPReturnValue = dbo.Error_RaiseError 5 @aSPMode            = @aSPMode
,    @aErrorSQLCodeblock    = @CodeBlockDesc
,    @aErrorSQLCategory    = @ErrorSQLCategory
,    @aErrorSQLProcedure    = @SPName
;

RETURN @ReturnValue;

Table of Contents


Posts in this Series


Comment on this post: