Stored Procedure Error Handling Best Practices, Part 1
Over the years I have worked with SQL Server a regular requirement was the ability to log errors in the database in a way that lets them be retrieved by support teams and allows end users to have a reference number.
This first part of this series will describe the design of the solution, the second part will describe the database objects, the third part will describe the implementation within stored procedures and the final part will demonstrate the solution.
RAISERROR
There are many possible solutions to this problem but the most common recommendation was to use RAISERROR. Solutions based on the use of RAISERROR did not provide a unique reference ID and therefore did not provide the level of information that was required.
Centralised Storage
A simple solution is to have a central error table that stores a row for each error. Whilst this will work very well, there are two reasons that it is not a complete solution:
- If transactions are in use then the logging to the error table would be rolled back if the transaction is rolled back.
- If the error occurred in a nested procedure call then there would be no trail back to the originating procedure call.
Multiple Tables
I first came across this requirement whilst using SQL Server 7 and the options that I used over different solutions included temporary tables and later permanent tables with multiple procedure calls.
By the time SQL 2005 was released and the try catch code block was introduced I had a stable solution that was in use through multiple versions of the application that I was working on which meant that it would be a while before I had the opportunity to re-visit this subject.
Table Types
One of the biggest promises of SQL 2008 was the introduction of Table Types that could be passed to stored procedures as parameters. This was a huge let down as the table types could be passed to and read by a procedure, but not populated by the procedure.
XML
At the time I was working with XML in SQL 2005 and this presented an option that I liked: store errors as XML until the transaction had been committed or rolled back, then write the errors into a permanent table.
The benefits of this were that one variable would be defined when a transaction is started, this variable would be passed into a procedure that logs an error and into any procedures called from the top level procedure.
Final Design
The final design consists of two tables (Error and ErrorSQL) which allows for other types of errors to be stored such as, for an ASP.NET application, 404 errors, 500 errors, etc. These can be stored in separate tables related to the Error table. There are two stored procedures and two error handling routines: one for stored procedures with transactions and one for stored procedures without transactions. There is also a set of procedures for testing the procedures and tidying the tables.