Procedure: [AdventureWorks2016CTP3].[dbo].[uspLogError]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [dbo] 
 Owner   [dbo] 
 Creation date   23.10.2015 
 Type   P 
 Encrypted   
 ID   661577395 
 Implementation type   Transact SQL 
 Is native compiled   

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description Datatype  Max length  Type  ReadOnly
@ErrorLogID   int 4 Input/output or output  

Collapse image Objects that depend on [dbo].[uspLogError]


Object name Object type Dep level
[dbo].[uspAddOrderTrackingEvent] Procedure 1
[HumanResources].[uspUpdateEmployeeHireInfo] Procedure 1
[HumanResources].[uspUpdateEmployeeLogin] Procedure 1
[HumanResources].[uspUpdateEmployeePersonalInfo] Procedure 1
[Purchasing].[dVendor] Trigger 1
[Sales].[iduSalesOrderDetail] Trigger 1
[Purchasing].[iPurchaseOrderDetail] Trigger 1
[Production].[iWorkOrder] Trigger 1
[Purchasing].[uPurchaseOrderDetail] Trigger 1
[Purchasing].[uPurchaseOrderHeader] Trigger 1
[Sales].[uSalesOrderHeader] Trigger 1
[Production].[uWorkOrder] Trigger 1
Total 12 object(s)

Collapse image Objects that [dbo].[uspLogError] depends on


Object name Object type Dep level
[dbo].[uspPrintError] Procedure 1
[dbo].[ErrorLog] Table 1
Total 2 object(s)

Collapse image Extended properties


Name  Value 
MS_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.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER 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
EXEC sp_addextendedproperty N'MS_Description', N'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.', 'SCHEMA', N'dbo', 'PROCEDURE', N'uspLogError', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_Description', N'Output parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.', 'SCHEMA', N'dbo', 'PROCEDURE', N'uspLogError', 'PARAMETER', N'@ErrorLogID'
GO

Collapse image See also


List of procedures