Exceptional Handling is not executing catch block

  • 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

     

    • This topic was modified 4 years, 10 months ago by  Susmith A.
  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • thanks

  • 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

  • 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