solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Devart - dbForge SQL Complete

dbForge SQL Complete is a code autocomplete tool for SQL Server Management Studio and Visual Studio. Free and advanced paid editions of this useful add-in offer powerful autocompletion and formatting of T-SQL code that replaces native Microsoft T-SQL Intellisense.

Learn more!

Standardized SQL Server Error Handling and Centralized Logging

By: Jeremy Kadlec | Read Comments | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

Related Tips: 1 | 2 | 3 | More

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:

Related Tips: 1 | 2 | 3 | More | Become a paid author

Last Update: 1/8/2007

Share:

Free SQL Server Learning

Comments and Feedback:

Sponsor Information

Try the free performance monitoring tool from Idera!

It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now.

Need SQL Server help and not sure where to turn? Reach out to the Edgewood experts for a Health Check starting at $995.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Learn SQL Server 2012, Performance Tuning, Development, Administration, Replication and more - free webcasts