SQL 2005: Use TRY/CATCH to handle and log errors

SQL 2005 supports TRY/CATCH to handle errors. It has a TRY block (BEGIN TRY/END TRY) and a CATCH block (BEGIN CATCH/END CATCH). If any exception happens in the try block, the execution will jump to the catch block. In the catch block, we can use the following SQL server functions to get the error information:

ERROR_NUMBER(): the error number

ERROR_SEVERITY

(): severity

ERROR_STATE

(): error state. Some error messages can be raised at multiple points in the code for the Microsoft SQL Server Database Engine. For example, an "1105" error can be raised for several different conditions. Each specific condition that raises the error assigns a unique state code.

ERROR_PROCEDURE(): the procedure that causes the exception. null for adhoc calls

ERROR_LINE

(): line number in the code that has the error

ERROR_MESSAGE

(): the error message

These functions can only work in a CATCH block. They return NULL in the outside of a CATCH block.

TRY/CATCH can be nested and the fundtions return the information for closet tier.

BEGIN TRY

END TRY

BEGIN CATCH

BEGIN TRY

END TRY

END CATCH

 

We can create a table to log for all SQL errors captured by catch blocks. The following are the table and stored procedures.

 

To log the errors, in stored procedures or queries, in the catch block, the proceure [dbo].[usp_SYS_LogErrors]  must be called. E.g.:

BEGIN TRY

INSERT tableName () VALUES (); — it causes PK error

END

TRY

BEGIN

CATCH

IF

@@TRANCOUNT>0 rollback

EXEC

[dbo].[usp_SYS_LogErrors]

END CATCH

 

SET ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblSYS_ErrorLogs](

[ErrorLogID] [int]

IDENTITY(1,1) NOT NULL,

[ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (getdate()),

[UserName] [sysname]

NOT NULL,

[ErrorNumber] [int]

NOT NULL,

[ErrorSeverity] [int] NULL,

[ErrorState] [int]

NULL,

[ErrorProcedure] [nvarchar]

(126) NULL,

[ErrorLine] [int]

NULL,

[ErrorMessage] [nvarchar]

(4000) NOT NULL,

CONSTRAINT [PK_tblSYS_ErrorLogs] PRIMARY KEY CLUSTERED

(

[ErrorLogID]

ASC

)

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY]

GO

SET ANSI_NULLS ON

SET

QUOTED_IDENTIFIER ON

go

 

CREATE PROCEDURE [dbo].[usp_SYS_LogErrors]

@ErrorLogID [int]

= 0 OUTPUT

AS

–$ usp_SYS_LogErrors logs error information in the tbl_SYS_ErrorLogs table about the

–$ error that caused execution to jump to the CATCH block of a

–$ TRY…CATCH construct. This should be executed from within the scope

–$ of a CATCH block after the transaction is rolled back. Otherwise it will return without inserting error

–$ information.

–//@ErrorLogID: it contains the ErrorLogID of the row inserted by [usp_SYS_LogErrors] in the tblSYS_ErrorLogs table.

BEGIN

SET NOCOUNT ON;

— Output parameter value of 0 indicates that error

— information was not logged

SET @ErrorLogID = 0;

BEGIN TRY

— Return if there is no error information to log

IF ERROR_NUMBER() IS NULL

RETURN;

— Return if inside an uncommittable transaction.

— Data insertion/modification is not allowed when

— a transaction is in an uncommittable state.

IF XACT_STATE() = 1

BEGIN

PRINT ‘Cannot log error since the current transaction is in an uncommittable state. ‘

+ ‘Rollback the transaction before executing uspLogError in order to successfully log error information.’;

RETURN;

END

DECLARE @ErrorLogIDs TABLE (ErrorLogID int NOT NULL)

INSERT [dbo].[tblSYS_ErrorLogs]

(

[UserName]

,

[ErrorNumber],

[ErrorSeverity]

,

[ErrorState]

,

[ErrorProcedure]

,

[ErrorLine]

,

[ErrorMessage]

)

OUTPUT INSERTED.ErrorLogID INTO @ErrorLogIDs

VALUES

(

CONVERT(sysname, CURRENT_USER),

ERROR_NUMBER

(),

ERROR_SEVERITY(),

ERROR_STATE

(),

ERROR_PROCEDURE

(),

ERROR_LINE

(),

ERROR_MESSAGE

()

);

— Pass back the ErrorLogID of the row inserted

SELECT TOP 1 @ErrorLogID = ErrorLogID FROM @ErrorLogIDs

END TRY

BEGIN CATCH

PRINT ‘An error occurred in stored procedure usp_SYS_LogErrors: ‘;

EXECUTE [dbo].[usp_SYS_PrintErrors];

RETURN 1;

END CATCH

END;

GO

CREATE

PROCEDURE [dbo].[usp_SYS_PrintErrors]

AS

BEGIN

SET NOCOUNT ON;

PRINT ‘User:’+CONVERT(sysname, CURRENT_USER);

PRINT ‘Error number:’+CAST(ERROR_NUMBER() as nvarchar(50));

PRINT ‘Error severity:’+CAST(ERROR_SEVERITY() as nvarchar(50));

PRINT ‘Error state:’+CAST(ERROR_STATE() as nvarchar(50));

PRINT ‘Error procedure name:’+ERROR_PROCEDURE();

PRINT ‘Error line number:’+CAST(ERROR_LINE() as nvarchar(50));

PRINT ‘Error message:’+ERROR_MESSAGE();

END

GO

 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

One Response to SQL 2005: Use TRY/CATCH to handle and log errors

  1. Youjin says:

    what is adhoc calls?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s