Often times errors in stored procedures go unnoticed. If there is no proper error handling on the application side or if the application is not doing any logging it is sometimes difficult to identify database errors especially if there is a lot of dynamic SQL involved. When all errors are logged to a central table, one can review them periodically and identify any potential issues and proactively fix them.

The following error handling method provides a clean way to catch and re-throw errors to the calling application and also log errors to a table if required. Errors would be logged to a central location (a table), which makes troubleshooting easy.

The method invloves creating a stored procedure (pReThrowErrorWithLogging) that will be called in the catch block to display, log, and re-throw errors. The stored procedure takes two parameters: @Debug when set to 1 will print the formatted error message and @LogError when enabled logs the error to the SPErrorLog table in the current database.

Here is the stored procedure.

/****************************************************************************
DESCRIPTION: Re-raises the error to be caught in the calling process.
Logs the error if @logError = 1
****************************************************************************/CREATE PROCEDURE [dbo].[pReThrowErrorWithLogging] 
  @ProcedureName VARCHAR(500),
  @logError BIT = 0,
  @Debug BIT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @ErrorNumber INT = ERROR_NUMBER()
DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
DECLARE @ErrorState INT = ERROR_STATE()
DECLARE @ErrorLine INT = ERROR_LINE()
DECLARE @ErrorProcedureName SYSNAME = ERROR_PROCEDURE()
DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE()
DECLARE @FullErrorMessage NVARCHAR(MAX)
SET @ErrorProcedureName = ISNULL(@ErrorProcedureName, @ProcedureName)
IF @Debug = 1
 PRINT N'ErrorNumber = '
  + CONVERT(NVARCHAR(10), @ErrorNumber)
  + N'ErrorSeverity = '
  + CONVERT(NVARCHAR(10), @ErrorSeverity)
  + N'ErrorState = '
  + CONVERT(NVARCHAR(10), @ErrorState)
  + N'ErrorLine = '
  + CONVERT(NVARCHAR(10), @ErrorLine)
  + N'ErrorProcedure = '
  + @ErrorProcedureName
  + N'ErrorMessage = '
  + @ErrorMessage + N'
 '
;
IF( @logError = 1 )
 BEGIN
  BEGIN TRY
   IF NOT EXISTS (SELECT 1
                    FROM sys.tables
                    WHERE name = 'SPErrorLog'
                  AND type IN ( N'U' ))
    BEGIN
     -- You can truncate this table periodically
     CREATE TABLE [SPErrorLog]
     (
      [ErrorLogID] [INT] IDENTITY(1, 1) NOT NULL,
      [ErrorTime] [DATETIME] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (getdate()),
      [UserName] [VARCHAR](500),
      [SPID] [INT],
      [ErrorNumber] [INT],
      [ErrorSeverity] [INT],
      [ErrorState] [INT],
      [ErrorProcedure] [NVARCHAR](400),
      [ErrorLine] [INT],
      [ErrorMessage] [NVARCHAR](4000),
      CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ( [ErrorLogID] ASC )
      )
    END
   INSERT INTO [SPErrorLog]
     (ErrorTime,
      UserName,
      SPID,
      ErrorNumber,
      ErrorSeverity,
      ErrorState,
      ErrorProcedure,
      ErrorLine,  
      ErrorMessage
      )
     VALUES ( GETDATE(),
              SYSTEM_USER,
              @@SPID,
              @ErrorNumber,
              @ErrorSeverity,
              @ErrorState,
              @ErrorProcedureName,
              @ErrorLine,
              LEFT(@ErrorMessage, 4000) 
            );
   END TRY
  BEGIN CATCH
   -- Do nothing, just re-throw the error.
  END CATCH
END
SET @FullErrorMessage = N'Unexpected error ocurred in Procedure: %s Line Number: %i Error Number: %i Severity: %i State: %i '
+ @ErrorMessage
RAISERROR (@FullErrorMessage,16,@ErrorState,@ErrorProcedureName,@ErrorLine,@ErrorNumber,@ErrorSeverity,@ErrorState)
RETURN @ErrorNumber
END
GO

In your stored procedures, use the following template for error handling  :

DECLARE @SPName NVARCHAR(392) = DB_NAME() + '.' + SCHEMA_NAME() + '.' + OBJECT_NAME(@@PROCID);
DECLARE @Result INT
BEGIN TRY
  -- Your code
  SET @Result = 0
END TRY
BEGIN CATCH 
 -- re-raise the error to be caught in the calling process
 EXECUTE @Result = dbo.pReThrowError @SPName = @SPName, @logError = 1, @Debug = 1
END CATCH;
RETURN @Result

Set @logError = 0 if you don’t want the error to be logged and use @Debug = 1 if you want to print the error.

Here is an example stored procedure that uses the pReThrowErrorWithLogging stored procedure to rethrow and log errors .

/*****************************************************************************************************
Object: [dbo].[pSimpleDivideToTestErrorLogging]
Description: Simple division sp to test pReThrowError with logging 
******************************************************************************************************/
ALTER PROCEDURE [dbo].[pSimpleDivideToTestErrorLogging]
  @Numerator INT
, @Denominator INT 
, @LogError BIT = 0 
, @Debug BIT = 0 
AS
BEGIN
SET NOCOUNT ON
DECLARE @Result INT
DECLARE @SPName NVARCHAR(392) = DB_NAME() + '.' + SCHEMA_NAME() + '.' + OBJECT_NAME(@@PROCID)
IF @Debug = 1 
 PRINT ': START OF ' + @SPName
 BEGIN TRY 
  SELECT @Numerator * 1.0/@Denominator;
  SET @Result = 0 ;
 END TRY
 BEGIN CATCH 
  -- re-raise the error to be caught in the calling process
  EXECUTE @Result = dbo.pReThrowErrorWithLogging @SPName,@LogError, @Debug
 END CATCH;
IF @Debug = 1 
 PRINT ': END OF '+ @SPName
RETURN @Result
END
GO

You can run the stored procedure with logging enabled by setting @LogError = 1 . This will log errors to the SPErrorLog table when an error is encountered.

EXEC [dbo].[pSimpleDivideToTestErrorLogging] 
  @Numerator = 1 
, @Denominator = 0
, @LogError = 1
, @Debug = 1;

Here is the output of the above run:

: START OF AIRProject.dbo.pSimpleDivideToTestErrorLogging

ErrorNumber = 8134

ErrorSeverity = 16

ErrorState = 1

ErrorLine = 24

ErrorProcedure = pSimpleDivideToTestErrorLogging

ErrorMessage = Divide by zero error encountered.

Msg 50000, Level 16, State 1, Procedure pReThrowErrorWithLogging, Line 96

Unexpected error ocurred in Procedure: pSimpleDivideToTestErrorLogging Line Number: 24 Error Number: 8134 Severity: 16 State: 1

Divide by zero error encountered.

: END OF AIRProject.dbo.pSimpleDivideToTestErrorLogging.

You can see the error logged in the SPError log table

SELECT *
 FROM SPErrorLog

If you want to make this error logging procedure a standard so that it can be used by all procs accessed by various applications, you have to create this application in each one of your databases and call the stored procedure in all of your error handling catch blocks.You can also create the stored procedure in the model database, so that the next time you create a new database this stored procedure is automatically there.

Some implementation options to consider :

• In SQL Server 2012 , you can use THROW instead of RAISEERROR to rethrow an error.
• You can have one error log table per database or have a central table per database or log errors to a remote central database via a linked server . Remember to use fully qualified name for the table if you want to use these options.