Error handling using TRY Catch in SQL Server

SQL Server 2008

Starting with SQL Server 2005, you have the ability to use TRY CATCH n you Transact-SQL. In a recent article by Ahmad Osama, he shows you some basic examples of how to use this method of trapping errors in your code.

USE [AdventureWorks2014]
GO
/****** Object: StoredProcedure [dbo].[uspLogError] Script Date: 10/5/2014 5:09:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- uspLogError logs error information in the ErrorLog 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 otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
   @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by uspLogError in the ErrorLog 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
   INSERT [dbo].[ErrorLog] 
   (
     [UserName], 
     [ErrorNumber], 
     [ErrorSeverity], 
     [ErrorState], 
     [ErrorProcedure], 
     [ErrorLine], 
     [ErrorMessage]
   ) 
     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
  SET @ErrorLogID = @@IDENTITY;
END TRY
BEGIN CATCH
  PRINT 'An error occurred in stored procedure uspLogError: ';
  EXECUTE [dbo].[uspPrintError];
  RETURN -1;
END CATCH
END;
Advertisements

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