Stored Procedure: dbo.uspLogError

Properties

Owner:	dbo
Encrypted:
Creation Date:	04/26/2006
Modification Date:
Description:	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

	Name	Direction	DataType	Length	Default	Description
	@ErrorLogID	OUTPUT	int	4		Output parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.

Total: 1 parameter(s)

Objects that [dbo].[uspLogError] depends on

Object Name	Owner	Object Type	Dep Level
ErrorLog	dbo	Table	1
uspPrintError	dbo	Procedure	1

Total: 2 object(s)

Objects that depend on [dbo].[uspLogError]

Object Name	Owner	Object Type	Dep Level
uspUpdateEmployeeHireInfo	HumanResources	Procedure	1
uspUpdateEmployeeLogin	HumanResources	Procedure	1
uspUpdateEmployeePersonalInfo	HumanResources	Procedure	1
dVendor	Purchasing	Trigger	1
iduSalesOrderDetail	Sales	Trigger	1
iPurchaseOrderDetail	Purchasing	Trigger	1
iStore	Sales	Trigger	1
iWorkOrder	Production	Trigger	1
uPurchaseOrderDetail	Purchasing	Trigger	1
uPurchaseOrderHeader	Purchasing	Trigger	1
uSalesOrderHeader	Sales	Trigger	1
uWorkOrder	Production	Trigger	1

Total: 12 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- uspLogError logs error information in the ErrorLog table about the
-- error that caused execution to jump to the CATCH block of a
-- TRY...CATCH construct. This should be executed from within the scope
-- of a CATCH block otherwise it will return without inserting error
-- information.
CREATE PROCEDURE [dbo].[uspLogError]
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
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]
            )
        VALUES
            (
            CONVERT(sysname, CURRENT_USER),
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO