Developers need to call the uspErrorHandling stored procedure in their code with the optional parameter list.
ALTER PROCEDURE [dbo].[uspErrorLogTest] (@date as datetime)
AS
--sample exec [uspErrorLogTest] '11/02/2018'
BEGIN TRY
declare @paramList varchar(4000) = '@date=' + cast( @date as varchar)
-- SELECT 1/0
select cast(@date as int) /0
END TRY
BEGIN CATCH
begin
EXEC dbo.uspErrorHandling @paramList
end
END CATCH
if object_id('uspErrorHandling','P') is not null
drop procedure uspErrorHandling
GO
CREATE procedure dbo.uspErrorHandling (@ErrorProcParameterList varchar(4000) = null)
AS
-- Declaration statements
DECLARE @ErrorNumber int
DECLARE @ErrorMessage varchar(4000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
DECLARE @ErrorProcedure varchar(200)
DECLARE @ErrorLine int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @TransactionDateTime datetime
-- Initialize variables
SELECT @ErrorNumber = isnull(error_number(),0),
@ErrorMessage = isnull(error_message(),'NULL Message'),
@ErrorSeverity = isnull(error_severity(),0),
@ErrorState = isnull(error_state(),1),
@ErrorLine = isnull(error_line(), 0),
@ErrorProcedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@TransactionDateTime = GETDATE();
select @ErrorMessage
-- Insert into the dbo.ErrorLog table
INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine,
ErrorProcedure, ErrorParameterList,UserName, HostName,TransactionDateTime)
SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine,
@ErrorProcedure, @ErrorProcParameterList,@UserName, @HostName, @TransactionDateTime
GO
--Table where we log the errors
CREATE TABLE [dbo].[ErrorHandling](
[ErrorHandlingID] [int] IDENTITY(1,1) NOT NULL,
[Error_Number] [int] NOT NULL,
[Error_Message] [varchar](4000) NULL,
[Error_Severity] [smallint] NOT NULL,
[Error_State] [smallint] NOT NULL,
[Error_Procedure] [varchar](200) NOT NULL,
[Error_Line] [int] NOT NULL,
[UserName] [varchar](128) NULL,
[HostName] [varchar](128) NULL,
[TransactionDateTime] [datetime] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ErrorHandlingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
