DEV4 Blog

  • Home
  • /
  • Blog
  • /
  • Set up custom error handling to log errors in SQL Server

1. Create an error table.


2. Set up a Stored Procedure to manage the error handling in your database.
CREATE PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted
                                  -- by uspLogError in the ErrorLog table.
AS
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],
            [ErrorTimeStamp]
            )
        VALUES
            (
            CONVERT(sysname, CURRENT_USER),
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE(),
            CURRENT_TIMESTAMP
            );

        -- Pass back the ErrorLogID of the row inserted
        SELECT @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
    PRINT 'An error occurred in stored procedure uspLogError: ';
        RETURN -1;
    END CATCH
END

3. Catch and record errors in SQL Procedures.

CREATE PROCEDURE [dbo].[uspMyProc] 
AS
  --@errorLogID used for error handling in CATCH statement
    DECLARE @ErrorLogID INT
      
   --Stored procedure code
      DECLARE @value1 INT

      SELECT @value1 = value FROM MyTable
END TRY
BEGIN CATCH
    --Catch is only triggered if the Try statement returns error
EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH

For further information on SQL Server error handling see SQL Server language elements.



Published on 12 Dec 2018

Add new comment...