How to capture exceptions which are raised during the execution of procedure via table

We need to create an exceptions Log table which will store all the exceptions raised by the procedure. This table will be a common table for all the procedure/jobs, which will capture data on every exception caused.

Below is the format of table which can be used to capture the same

CREATE TABLE Errors_Procedures

(Error_ID INT IDENTITY(1, 1), -- unique id for error table

Procedure_Name NVARCHAR(150),-- this column will store the procedure name

Proc_Error_Number INT,-- this column will store the Error Number while executing

Proc_Error_State INT,---- this column will store the Error state while executing

Proc_Error_Severity INT,-- this column will store the Error Severity while executing

Proc_Error_Line INT, -- this column will store the Error line number on which is occurred while executing

Proc_Error_Message NVARCHAR(4000), -- this column will store Error message which is in detail can which will help to check the issue

Error_Date_Time DATETIME) ---- this column will store the time at which error is raised while executing, this column can also be set as getdate() as a default column value

Create proc usp_exception_handle

As

Begin try

Select 2/0

RAISERROR ('Error Message which User want to send while raising the error', 16, 1)

End try

BEGIN CATCH

Insert into Errors_Procedures

(Procedure_Name, Proc_Error_Number, Proc_Error_State, Proc_Error_Severity, Proc_Error_Line, Proc_Error_Message, Error_Date_Time)

select ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_STATE(),ERROR_SEVERITY(),ERROR_LINE(),ERROR_MESSAGE(), GETDATE()

END CATCH

Exec usp_exception_handle

Select * from Errors_Procedure