Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Standardized SQL Server Error Handling and Centralized Logging

By:   |   Updated: 2007-01-08   |   Comments   |   Related: 1 | 2 | 3 | More > Error Handling


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.


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

Sample TRY\CATCH code

   T-SQL Statement
   T-SQL Statement

Sample stored procedure

CREATE procedure dbo.spErrorHandling 

-- 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,
[pkErrorHandlingID] ASC

Verification process

-- Sample procedure 
   SELECT 1/0

   EXEC dbo.spErrorHandling 

-- 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
  • Single table per environment (development, test, production or application type)
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:

Last Updated: 2007-01-08

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools