May 6, 2019 at 12:29 pm
Below is the sample stored procedure and tale for error logging
error table:
CREATE TABLE [dbo].[ErrorLog](
[ErrorID] [bigint] IDENTITY(1,1) NOT NULL,
[LogDate] [datetime] NULL DEFAULT (getdate()),
[ErrorProcedure] [varchar](100) NULL,
[ErrorLine] [int] NULL,
[ErrorDescription] [varchar](4000) NULL,
[Parameter Details] [varchar](4000) NULL,
)
sp for error log :
CREATE PROCEDURE [dbo].[spInsertErrors]
@ParamDetails varchar(4000) = null
AS
BEGIN
SET NOCOUNT ON;
INSERT into [dbo].[ErrorLog]
(
[ErrorProcedure] ,
[ErrorLine] ,
[ErrorDescription] ,
[Parameter Details]
)
select
ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS [ErrorDescription]
,@ParamDetails as [Parameter Details]
END
GO
dummy sp i am using for testing error handling mechanism :
CREATE PROCEDURE [dbo].[ExampleProc]
(@date date)
AS
begin try
--declare @date date = '13-13-2018'
select convert(date,@date,120)
end try
begin catch
exec spInsertErrors
end catch
GO
My question is when I am passing invalid date (some thing like exec [ExampleProc] '23-23-2018') . Catch block is not getting executed instead it is throwing error ' Msg 8114, Level 16, State 1, Procedure ExampleProc, Line 0
Error converting data type varchar to date'
error log SP is working fine when i checked for other scenarios
May 6, 2019 at 1:35 pm
I think this is right ...
As you have defined the datatype of the input parameter to be date, the error you see happens before the proc executes. You need to add error handling to whatever is calling the proc too. Or change the datatype of the input parameter and then validate within the proc.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 6, 2019 at 6:14 pm
thanks
May 6, 2019 at 9:06 pm
Just another note:
This method will 'fail' if an explicit transaction is started - and then rolled back. Anything written to the table during the open transaction would be rolled back.
The way to avoid that is to use a table variable - which isn't part of the transaction. The table variable can then be used to capture the error message information, the transaction is then rolled back - and finally the call to insert the data into your error table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 7, 2019 at 12:14 pm
Thanks for suggestion
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply