SQL Server: Error Logging and Reporting within a Stored Procedure

Posted March 2, 2015 by Vishwanath Dalvi in Database, SQL Server

feature-sql

As the number of stored procedures increases in a database, the burden of managing those stored procedures increases, too. On a production database, thousands of procedures are executed daily, so you are required to know why a stored procedure failed at a certain time. This can be done by implementing error logging and reporting within each stored procedure.

Once an issue has been raised on a production database, you need to solve it immediately to stop loss to a business. To monitor and resolve stored procedure errors, first you need to log if any error occurs and then monitor and perform error reporting.

Let us create a simple procedure for division calculation.

CREATE PROCEDURE dbo.MathCalculation
(
    @Dividend INT, 
    @Divisor INT
)
AS
BEGIN
SET NOCOUNT ON;
    BEGIN TRY
      SELECT @[email protected] as Quotient;
    END TRY
    BEGIN CATCH
     PRINT Error_message();
    END CATCH
SET NOCOUNT OFF;
END  
GO

 

I have created a simple stored procedure to divide two numbers and get their quotient. Let us see how it works when we perform divide by 0 operation.

EXEC dbo.MathCalculation 100, 2 -- Works perfectly giving 50 as quotient

EXEC dbo.MathCalculation 100, 0 -- Divide by zero error encountered

 

sql server error reporting in stored procedures divide by zero exception

The above calculation fails and prints an error message in an error message window Divide by zero error encountered.

Now you are seeing errors on-screen, but in a production environment, you do not get such flexibility. Therefore, we need to implement error logging.

Let us create a table to log all stored procedure errors.

CREATE TABLE [dbo].[LearningErrorLog]
(
    [ErrorID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ErrorNumber] [nvarchar](50) NOT NULL,
    [ErrorDescription] [nvarchar](4000) NULL,
    [ErrorProcedure] [nvarchar](100) NULL,
    [ErrorState] [int] NULL,
    [ErrorSeverity] [int] NULL,
    [ErrorLine] [int] NULL,
    [ErrorTime] [datetime] NULL    
);

 

Let us create a stored procedure to log errors.

CREATE PROCEDURE [dbo].[Learning_Insert_StoredProcedure_ErrorLog]
AS
BEGIN
SET NOCOUNT ON 
        
         INSERT INTO [LearningErrorLog]  
             (
             ErrorNumber 
            ,ErrorDescription 
            ,ErrorProcedure 
            ,ErrorState 
            ,ErrorSeverity 
            ,ErrorLine 
            ,ErrorTime 
           )
           VALUES
           (
             ERROR_NUMBER()
            ,ERROR_MESSAGE()
            ,ERROR_PROCEDURE()
            ,ERROR_STATE()
            ,ERROR_SEVERITY()
            ,ERROR_LINE()
            ,GETDATE()  
           );
    
SET NOCOUNT OFF    
END

 

I have called built-in error reporting functions in the above stored procedure.

ERROR_NUMBER() – returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run

ERROR_MESSAGE() – returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run

ERROR_PROCEDURE() – returns the name of the stored procedure or trigger where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run

ERROR_STATE() – returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run

ERROR_SEVERITY() – returns the severity of the error that caused the CATCH block of a TRY…CATCH construct to be run

GETDATE() – returns the time of the error that caused

Now, we can query [LearningErrorLog] table to monitor the logged error.

Let us modify our MathCalculation stored procedure and call [Learning_Insert_StoredProcedure_ErrorLog] within catch block to perform error logging if any error occurred within stored procedure.

ALTER PROCEDURE dbo.MathCalculation
(
    @Dividend INT, 
    @Divisor INT
)
AS
BEGIN
SET NOCOUNT ON;
    BEGIN TRY
      SELECT @[email protected] as Quotient;
    END TRY
    BEGIN CATCH
  EXEC [dbo].[Learning_Insert_StoredProcedure_ErrorLog] --To log Stored procedure errors
    END CATCH
SET NOCOUNT OFF;
END  
GO

 

Now, again execute error generation scenario.

EXEC dbo.MathCalculation 100, 0 -- divide by 0 error scenario 

 

Query the [LearningErrorLog] table. You can see the catch exception stored procedure error has been logged in the table.

error log table stored procedure

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.