Create one StoredProcedure for SQL Try-Catchs

[bezzad]

When us want to run a SP, maybe that codes have some problem and cause to throw an errors in the SQL. So this is better to have one Stored Procedures to log the exception informations on the ErrorLog table, without to know what is codes exist in the SP bodies.

[Hamcker]

یاشا کیشی

-----Original Message-----
From: "Behzad Khosravifar" notifications@github.com
Sent: ‎5/‎15/‎2015 10:55 AM
To: "Behzadkhosravifar/ErrorControlSystem" ErrorControlSystem@noreply.github.com
Subject: [ErrorControlSystem] Create one StoredProcedure for SQL Try-Catchs(#54)

When us want to run a SP, maybe that codes have some problem and cause to throw an errors in the SQL. So this is better to have one Stored Procedures to log the exception informations on the ErrorLog table, without to know what is codes exist in the SP bodies.
—
Reply to this email directly or view it on GitHub.

[bezzad]

One SP by name 'sp_CatchError' created to catch any SQL Error without passing parameters.

sp_CatchError Body is:

CREATE PROCEDURE [dbo].[sp_CatchError] 
@RaisError bit
AS
BEGIN
    DECLARE 
        @DatabaseName NVARCHAR(max) = IsNull(Original_DB_NAME(), DB_NAME()),
        @ERROR_NUMBER INT = ERROR_NUMBER() , -- @@ERROR
        @ERROR_STATE INT = ERROR_STATE() ,
        @ERROR_SEVERITY INT = ERROR_SEVERITY(),
        @ERROR_LINE INT = ERROR_LINE() , 
        @ERROR_Column INT = 0,
        @ERROR_PROCEDURE SysName = ERROR_PROCEDURE() ,  
        @ERROR_MESSAGE NVARCHAR(max) = ERROR_MESSAGE(),         
        @Server_Instance NVARCHAR(1024) = @@SERVERNAME + '' \ '' + @@ServiceName,
        @IP_Address SysName = (SELECT client_net_address FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID),
        @MAC_Address SysName = (SELECT net_address from sysprocesses where spid = @@SPID),
        @Culture SysName = @@LANGUAGE,
        @OS NVARCHAR(max) = @@Version,
        @ClrVersion SysName = (SELECT CONVERT(sysname, SERVERPROPERTY(''BuildClrVersion''))),
        @ErrorDate DateTime = GetDate(),
        @IsHandled bit = 1,
        @ErrorType SysName = ''SqlException'',
        @UserName SysName = suser_sname(),
        @MemberType SysName = ''Stored Procedure'';

    IF @ERROR_NUMBER <> 50000 
        -- Check the error exist or not? if exist then only update that 
        IF ( Select COUNT(ErrorId) FROM [ErrorLog]  
                WHERE HResult = @ERROR_NUMBER AND  
                    Line = @ERROR_LINE AND
                    Method = @ERROR_PROCEDURE AND 
                    [User] = @UserName) > 0 
            -- Update error object from ErrorLog table 
            UPDATE dbo.ErrorLog SET DuplicateNo += 1  
                WHERE 
                    HResult = @ERROR_NUMBER AND  
                    Line = @ERROR_LINE AND
                    Method = @ERROR_PROCEDURE AND 
                    [User] = @UserName;
        ELSE 
            BEGIN
                INSERT  INTO UsersManagements.dbo.ErrorLog 
                        (  
                            [OS],
                            [User],
                            [CLRVersion],
                            [ErrorDateTime],
                            [IsHandled],
                            [Type], 
                            [Line], 
                            [Column],
                            [Message], 
                            [HResult], 
                            [Source],
                            [Method],
                            [ModuleName],
                            [IPv4Address],
                            [MACAddress],
                            [MemberType],
                            [CurrentCulture],
                            [DuplicateNo],
                            [Data] 
                        ) 
                VALUES  (  
                            @OS,
                            @UserName,
                            @ClrVersion,
                            @ErrorDate,
                            @IsHandled,
                            @ErrorType, 
                            @ERROR_LINE, 
                            @ERROR_Column,
                            @ERROR_MESSAGE, 
                            @ERROR_NUMBER, 
                            @DatabaseName,
                            @ERROR_PROCEDURE,
                            @Server_Instance,
                            @IP_Address,
                            @MAC_Address,
                            @MemberType,
                            @Culture,
                            0,
                            ( 
                                SELECT 
                                    @ERROR_SEVERITY [SEVERITY],
                                    @ERROR_STATE [STATE]
                                FOR 
                                XML PATH('''') ,
                                    ROOT(''Error'') 
                            ) 
                        ) 
            END

        If @RaisError = 1
            RAISERROR(@ERROR_MESSAGE, 18, 255) 
END

[bezzad]

How to use this SP

One Stored Procedure example with sp_CatchError usage:

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_TestThrowError]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_TestThrowError] 
As
BEGIN  
    BEGIN TRY   
        BEGIN TRANSACTION
            DECLARE @None INT;

            SET @None = 1 / 0;    -- THROW 40000, 'Divide by zero exception' , 1

            RETURN @None       
        COMMIT TRANSACTION 
    END TRY 
    BEGIN CATCH
        ROLLBACK TRANSACTION 

        Exec UsersManagements.dbo.sp_CatchError @RaisError = 0   -- RaisError or Not
    END CATCH 
END

[Hamcker]

as an aside, How can I send my custom Data to sp_CatchError?
I Mean is there any way to pass data into sp_CatchError?

UPDATE:
There are two capability I suggest:

1. Make a way to pass custom Data to sp_CatchError
2. Return back the inserted error ID so I will be able to report it to my users for trace relateds

[bezzad]

Yes, is good idea to add custom data into error log table by this SP. i try it again...

[bezzad]

One Parameters by name @ExtraData nvarchar(max) added to sp_CatchError for attach extra data to Data (xml) fields.

For example to use that:

Begin TRY
    -- Throw one exception to test
    SELECT 1 /0
END TRY
Begin CATCH
    EXEC UsersManagements.dbo.sp_CatchError @RaisError = 0, -- bit
        @ExtraData = N'test d' -- nvarchar(max) 
END Catch