One the responsibilities for individuals working with relational databases and writing stored procedures, is watching SPs to make sure they work flawlessly. And in instances where they don't, leave trail of bread crumbs to track and debug why they failed.
Stored procedures can be complex with many dependencies on tables, functions and complex data transformations and unintentional changes to any of the underlying objects, such as column name changes, introduces potential for the SPs to fail. Proper logging leaves bread crumbs, which from forensic analysis might provide insight on why it failed and save valuable work hours.
An ideal solution to this would be a logging table that provides insights into the who, where, when and why the error occurred. There are many ways to solve this problem. This is just one approach that has worked for me and provides the most flexibility.
Set Up
The script below creates the initial table that will be populated with the logged information.
Tables And Indexes
Stored Procedure to Call to Generate Error Statement
Stored Procedure to Log the Event
Logging Example
Below is a simple example on how to use the logging procedure and the what the expected out put is in case of any error. For demonstration purpose I created a simple procedure which result in an error by 0 error.
Once you execute the procedure the logging function adds a new line to the logEvent table with the error information.
One thing to note is the eventErrorInfo column, the error message is formatted as json with all the relevant information. This can be parsed out into a key value column format using the following query.
Results
The advantage of using the JSON format to store data is that it provides flexibility into the information that can be tracked. Because of the key value relationship, the attributes that are tracked for each procedures can vary, such as tracking the procedure parameters input or row counts.