Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Re-throw Errors with Logging

By Harsha Majety,

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.

Total article views: 5346 | Views in the last 30 days: 9
 
Related Articles
FORUM

Error in Stored Procedure

Error in COALESCE stored Procedure

FORUM

Stored procedure error handler

Stored procedure error handler

FORUM

getting in error in using sp_executesql like Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

error like Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

FORUM

Error converting data type nvarchar to float

Error converting data type nvarchar to float

FORUM

Nested Stored procedure

Error In nested Stored Procedure

Tags
error handling    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones