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:
Post a Comment