ErrorControlSystem
ErrorControlSystem copied to clipboard
bezzad
Create one StoredProcedure for SQL Try-Catchs
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.
یاشا کیشی
-----Original Message----- From: "Behzad Khosravifar" [email protected] Sent: 5/15/2015 10:55 AM To: "Behzadkhosravifar/ErrorControlSystem" [email protected] 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.
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
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
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:
- Make a way to pass custom
Datatosp_CatchError - Return back the inserted error
IDso I will be able to report it to my users for trace relateds
Yes, is good idea to add custom data into error log table by this SP. i try it again...
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
Yes, SP must be return inserted record ID to the user procedures. I will to solve that as soon my friend.
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
Thank you too. I will definitely use it. What is the problem at SP return a value ? Or is it better?
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.
Actually there are some limitations on scalar function, like Its IMPOSSIBLE to Insert into table in a scalar function.
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
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.