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.
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;