SQL Server: Error Logging and Reporting within a Stored Procedure

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 @Dividend/@Divisor 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

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 @Dividend/@Divisor 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.