Welcome Guest, you are in: <root> • Login

Page History: dbo uspLogError (procedure)

Compare Page Revisions

3 2 1 0 Current 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: 2010/02/11 09:04

wikibot ¶

Procedure	dbo.uspLogError
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.

Source ¶

-- 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;

References ¶

Dependency Type	Object Type	Referenced Object
Insert	Table	dbo.ErrorLog
Execute	Procedure	dbo.uspPrintError

Dependencies ¶

Reference Type	Object Type	Referencing Object	Child Type	Child Object
Execute	Procedure	HumanResources.uspUpdateEmployeeHireInfo
Execute	Procedure	HumanResources.uspUpdateEmployeeLogin
Execute	Procedure	HumanResources.uspUpdateEmployeePersonalInfo
Execute	Table	Production.WorkOrder	Trigger	iWorkOrder
Execute	Table	Production.WorkOrder	Trigger	uWorkOrder
Execute	Table	Purchasing.PurchaseOrderDetail	Trigger	iPurchaseOrderDetail
Execute	Table	Purchasing.PurchaseOrderDetail	Trigger	uPurchaseOrderDetail
Execute	Table	Purchasing.PurchaseOrderHeader	Trigger	uPurchaseOrderHeader
Execute	Table	Purchasing.Vendor	Trigger	dVendor
Execute	Table	Sales.SalesOrderDetail	Trigger	iduSalesOrderDetail
Execute	Table	Sales.SalesOrderHeader	Trigger	uSalesOrderHeader
Execute	Table	Sales.Store	Trigger	iStore