By: Jeremy Kadlec | Comments | Related: 1 | 2 | 3 | > Error Handling
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:
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips