Stored Procedure Error Handling Best Practices, Part 2
In the 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 this part of the series, I explain the database objects that are required for this solution. The next part of the series will cover the implementation of the solution within stored procedures.
Logic
The logic is relatively simple:
- A stored procedure begins a transaction
- An xml variable is declared that will store all errors that occur
- If an error occurs, it is captured and written to the xml variable
- Nested procedures are called
- The xml variable is passed to any procedures called within the top procedure
- Errors occurring in nested procedures are written to the xml variable
- The xml variable is returned to the top procedure
- If no error has occurred
- The transaction is committed
- If an error has occurred
- The transaction is rolled back
- The contents of the xml variable are written to the error tables
Tables
I use two tables: Error and ErrorSQL. This model can be expanded to include other error types such as ErrorNET or Error404.
Error
The Error table has a primary key of ErrorID which is an integer and is easily understood and communicated by end users. There is also a ErrorGUID which is used to correlate rows in ErrorSQL and could be used for any other child tables. ErrorGUID has a unique constraint that is referenced by a foreign key from ErrorSQL
The Error table uses a reference table of ErrorTypeID and can be expanded with fields like ErrorIsResolvedTF or an identifier for a bug tracking system.
ErrorSQL
The fields in the ErrorSQL table are derived from the values available in the catch part of the try/catch code block. The additional fields are ErrorSQLID which combines with ErrorID to form the primary key. The ErrorSQLCodeBlock field contains a dexcription of the code block where the error occurred and allows a quick view of the cause of the the error without having to rely on the line number alone. The ErrorSQLCategory field allow a quick assessment of the type of error. I use the following categories:
- No rows affected – This is a soft, catchable error that usually results from a check of @@rowcount.
- Soft error – An error that is caught and handled and does not cause the rollback of a transaction.
- Hard error – An error that is caught and handled and requires the rollback of the transaction.
- Fatal error – An error caught through the try/catch mechanism that requires the rollback of the transaction.
Stored Procedures
This solution uses three procedures to handle the errors:
- Error_SQLHandler – Used to add errors to an XML variable
- Error_SQLCommit – Used to write the errors from the XML variable to the Error and ErrorSQL tables.
- Error_RaiseError – Used to raise an error and write it to the event log when, for any reason the errors cannot be written to the normal tables.
Both Error_SQLHandler and Error_SQLCommit use XML functions and therefore they need to have QUOTED_IDENTIFIER set to ON.
Error_SQLHandler
This procedure takes three variables that are defined in the calling procedure and populated at the point that the error occurs. It also accepts @aErrorLog which is an xml variable that may or may not contain errors.
The purpose of this procedure is to check if an error has been caught and if so get the details from the error functions such as ERROR_NUMBER(). The error is then added to the end @aErrorlog xml which is then returned to the calling procedure.
Error_SQLCommit
This procedure accepts the @aErrorLog xml variable as an input, creates a row in Error, creates rows in ErrorSQL related to each error in @aErrorLog and returns the ErrorID from the Error table.
Error_RaiseError
This procedure is used to raise an error and write it to the event log. It simply retrieves information from the parameters passed in, checks for any error information, builds a message and raises the error.
In the next part of this series I will step through the implementation of the error handling within stored procedures containing transactions, and stored procedures called be procedures containing transactions.