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

[Hamcker]

Thanks a lot 👍
What about the second one? ID of inserted row?

[bezzad]

Yes, SP must be return inserted record ID to the user procedures.
I will to solve that as soon my friend.

[Hamcker]

Thank you :)
As an offer you can use OUTPUT INSERTED.ErrorId INTO @OutTable right after insert column names.
while @OutTable is defined as below:

DECLARE @OutTable TABLE (ErrorId BIGINT)

and the consider to doing something like this:

SELECT @OutParameter = ErrorId FROM @OutTable

[bezzad]

Thank you too.
I will definitely use it.
What is the problem at SP return a value ? Or is it better?

[bezzad]

Do you think is better than it was a scalar function?

[Hamcker]

If you mean using SELECT statement in SP's body, SQL Server will write the result directly into output context thus developers couldn't catch the value!
Using an output parameter let's developers to handle the returned value.

[Hamcker]

Actually there are some limitations on scalar function, like Its IMPOSSIBLE to Insert into table in a scalar function.

[bezzad]

OK, so i used OUTPUT arguments to return ID.
thank you again.

[bezzad]

Now you can use this model:

BEGIN TRY
    -- Throw Divid by zero exception for test
    SELECT 1 / 0
END TRY
Begin CATCH
    DECLARE @id BIGINT

    EXECUTE UsersManagements.dbo.sp_CatchError @RaisError = NULL, -- bit
        @ExtraData = N'test', -- nvarchar(max)
        @ErrorId = @id OUTPUT -- bigint

    SELECT * FROM UsersManagements.dbo.ErrorLog WHERE ErrorId = @id 
END CATCH

[Hamcker]

thank you Behzad, as we talked before, it's impossible to access local functions like ERROR_MESSAGE() from a linked server to handle SQL errors data. please add some inputs to the stored procedure sp_CatchError to pass the values manually.

[bezzad]

I try to add this feature as soon. thanks a lot for your attentions