September 1, 2016

Capture exception in Store Procedure


Note: error log table Store Procedure which need to be called in exception block

CREATE TABLE [dbo].[GHMS_ErrorLog](
[ErrorID] [bigint] IDENTITY(1,1) NOT NULL,
[ErrorNumber] [nvarchar](50) NOT NULL,
[ErrorDescription] [nvarchar](4000) NULL,
[ErrorProcedure] [nvarchar](100) NULL,
[ErrorState] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorLine] [int] NULL,
[ErrorTime] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ErrorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Create PROCEDURE [G_StoredProcedure_ErrorLog]
AS
BEGIN
SET NOCOUNT ON
       
         INSERT INTO [GHMS_ErrorLog]
             (
             ErrorNumber
            ,ErrorDescription
            ,ErrorProcedure
            ,ErrorState
            ,ErrorSeverity
            ,ErrorLine
            ,ErrorTime
           )
           VALUES
           (
             ERROR_NUMBER()
            ,ERROR_MESSAGE()
            ,ERROR_PROCEDURE()
            ,ERROR_STATE()
            ,ERROR_SEVERITY()
            ,ERROR_LINE()
            ,GETDATE()
           );
   
SET NOCOUNT OFF  
END


write in procedure

BEGIN TRY
END TRY
 BEGIN CATCH
        BEGIN
            EXECUTE GHMS_StoredProcedure_ErrorLog ;
            SET @errorMessage = 'Database Error Occured, !';
        END
    END CATCH

No comments: