Standardized SQL Server Error Handling and Centralized Logging

Problem

Error handling was not always used in SQL Server 2000 because the techniques were cumbersome. With SQL Server 2005 the TRY and CATCH syntax is available which simplifies the error handling process in all T-SQL code. To take the error handling to the next level, why not standardize the error handling across your T-SQL code and centralize the location of the errors? Seem like a good idea, right? So let’s jump into how to do this.

Solution

Let’s break this down how to setup the error handling into the following steps:

Sample TRY\CATCH code
- For background information on TRY\CATCH visit – SQL Server 2005 – Try Catch Exception Handling
Sample stored procedure
Sample logging table
Verification process
Implementation options

Sample TRY\CATCH code

 BEGIN TRY 
   T-SQL Statement
END TRY
BEGIN CATCH
   T-SQL Statement
END CATCH

Sample stored procedure

 CREATE procedure dbo.spErrorHandling 
AS 
-- Declaration statements
DECLARE @Error_Number int
DECLARE @Error_Message varchar(4000)
DECLARE @Error_Severity int
DECLARE @Error_State int
DECLARE @Error_Procedure varchar(200)
DECLARE @Error_Line int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @Time_Stamp datetime
-- Initialize variables
SELECT @Error_Number = isnull(error_number(),0),
@Error_Message = isnull(error_message(),'NULL Message'),
@Error_Severity = isnull(error_severity(),0),
@Error_State = isnull(error_state(),1),
@Error_Line = isnull(error_line(), 0),
@Error_Procedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@Time_Stamp = GETDATE();
-- Insert into the dbo.ErrorHandling table
INSERT INTO dbo.ErrorHandling (Error_Number, Error_Message, Error_Severity, Error_State, Error_Line, 
Error_Procedure, UserName, HostName, Time_Stamp)
SELECT @Error_Number, @Error_Message, @Error_Severity, @Error_State, @Error_Line, 
@Error_Procedure, @UserName, @HostName, @Time_Stamp

Sample logging table

 CREATE TABLE [dbo].[ErrorHandling](
[pkErrorHandlingID] [int] IDENTITY(1,1) NOT NULL,
[Error_Number] [int] NOT NULL,
[Error_Message] [varchar](4000) COLLATE Latin1_General_BIN NULL,
[Error_Severity] [smallint] NOT NULL,
[Error_State] [smallint] NOT NULL DEFAULT ((1)),
[Error_Procedure] [varchar](200) COLLATE Latin1_General_BIN NOT NULL,
[Error_Line] [int] NOT NULL DEFAULT ((0)),
[UserName] [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),
[HostName] [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),
[Time_Stamp] datetime NOT NULL,
PRIMARY KEY CLUSTERED
(
[pkErrorHandlingID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Verification process

 -- Sample procedure 
CREATE PROCEDURE dbo.spTest 
AS
BEGIN TRY
   SELECT 1/0
END TRY
BEGIN CATCH
   EXEC dbo.spErrorHandling 
END CATCH
-- Execute the sample procedure
EXEC dbo.spTest;
-- Review the error handling data captured
SELECT * FROM dbo.ErrorHandling

Implementation options

Below are some options to implement the standardized error logging:

Single table per database
- Shown in the example above
Single table per SQL Server
- Be sure to qualify the objects with the database name
Single table per application if the application is distributed
- Be sure to qualify the objects with the four part name
Single table per environment (development, test, production or application type)
- Be sure to qualify the objects with the four part name

Next Steps

As you begin to deploy code for SQL Server 2005, think how to architect the error handling portion of the application. Consider this technique or something custom to meet your organizational error handling needs.
With the TRY and CATCH paradigm, the error handling solution in SQL Server can be much easier and more straight forward to implement, so be sure to standardize your code. Do not settle for any code deployed to production without error handling.
When you think about error handling, be sure to think past the development cycles to the operational impacts from the errors. The additional columns in the table above provide the opportunity to not only capture the error, but support basic issue management as well.
For additional information on error handling with SQL Server 2005, check out these tips on MSSQLTips.com:

Resources

Community

Subscribe