CREATE PROCEDURE [dbo].[P_AddErrorLog]
@IsRaiseErrorOn BIT = 1
, @IsRaiseErrorWithLogOn BIT = 0 — Ignored where @IsRaiseErrorOn = 0
, @IsRollBackTranOn BIT = 1 — Ignored where XACT_STATE() = -1. It will be Rollback anyway.(Recommanded always turn on “@IsRollBackTranOn=1” since error logging will be rollback as well in parent SP.
, @CustomMessage NVARCHAR(4000) = NULL — Anyother message want to store such as parameter and e.t.c..
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @intReturnValue int;

DECLARE
@nvcErrorMessage nvarchar(4000),
@intErrorNumber int,
@intErrorSeverity int,
@intErrorState int,
@intErrorLine int,
@nvcErrorProcedure nvarchar(128);

/**_# Log the error.*/
/**_## Assign variables to error-handling functions that capture information for RAISERROR.*/
SET @intErrorNumber = ERROR_NUMBER();
SET @intErrorSeverity = ERROR_SEVERITY();
SET @intErrorState = ERROR_STATE();
SET @intErrorLine = ERROR_LINE();
SET @nvcErrorProcedure = ERROR_PROCEDURE();
SET @nvcErrorMessage = ISNULL(ERROR_MESSAGE(),’NULL’);

/**_# If there is no error information to log, return 0.*/
IF @intErrorNumber IS NULL BEGIN
SET @intReturnValue = 0;
GOTO Done;
END
ELSE BEGIN
SET @intReturnValue = @intErrorNumber;
END
/**_# Rollback and return if inside an uncommittable transaction.*/
IF XACT_STATE() = -1 BEGIN
BEGIN TRAN –Trick to remove this error: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
ROLLBACK TRANSACTION;
SET @nvcErrorMessage = ‘[System : uncommittable transaction] ‘+ @nvcErrorMessage;
END

/**_# Rollback and return if @IsRollBackTranOn and Trancount>0.*/
IF @IsRollBackTranOn = 1 AND @@TRANCOUNT > 0
BEGIN
BEGIN TRAN –Trick to remove this error: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
ROLLBACK TRANSACTION;
END

/**_## Log the error that occurred.*/
INSERT dbo.ErrorLogs (LoginName, HostName, ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, CustomMessage, Registered_DateTime)
VALUES (CAST(ORIGINAL_LOGIN() AS nvarchar(128)), CAST(HOST_NAME() AS nvarchar(128)), @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine, @nvcErrorMessage, @CustomMessage, GETDATE());

/**_# Rethrow the error.*/
IF @IsRaiseErrorOn = 1 BEGIN
SET @nvcErrorMessage = N’Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ‘ + @nvcErrorMessage;

IF @IsRaiseErrorWithLogOn = 1 BEGIN
RAISERROR (@nvcErrorMessage, @intErrorSeverity, 1, @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine) WITH LOG;
END ELSE BEGIN
RAISERROR (@nvcErrorMessage, @intErrorSeverity, 1, @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine);
END
END

Done:
RETURN @intReturnValue;

END