Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened, or it could be us trying to fix an error. It can even be translating the error in SQL language because we all know how technical SQL Server error messages could get making no sense and hard to understand. Luckily, we have a chance to translate those messages into something more meaningful to pass on to the users, developers, etc.

Database Table to Record Errors

The script below creates a table called DB_Errors, which can be used to store tracking data:

CREATE TABLE dbo.DB_Errors (
	ErrorID        INT IDENTITY(1, 1) PRIMARY KEY,
	UserName       VARCHAR(100),
	ErrorNumber    INT,
	ErrorState     INT,
	ErrorSeverity  INT,
	ErrorLine      INT,
	ErrorProcedure VARCHAR(MAX),
	ErrorMessage   VARCHAR(MAX),
	ErrorDateTime  DATETIME
);

Stored Procedure

The script below gives you an example on how to structure your Stored Procedure to incorporate error handling

TRUNCATE TABLE dbo.DB_Errors;

-- 

BEGIN TRY
	-- Your Stored Procedure SQL Code
END TRY
BEGIN CATCH
INSERT INTO dbo.DB_Errors
	VALUES (
		SUSER_SNAME(),
		ERROR_NUMBER(),
		ERROR_STATE(),
		ERROR_SEVERITY(),
		ERROR_LINE(),
		ERROR_PROCEDURE(),
		ERROR_MESSAGE(),
		GETDATE());
END CATCH

--

Source

https://www.sqlshack.com/how-to-implement-error-handling-in-sql-server/