BEGIN CATCH
	EXEC dbo.ExceptionHandler;
END CATCH

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.ExceptionHandler
	@ProcID int = NULL
	,@ExtraMessage nvarchar(1000) = NULL
AS

SET NOCOUNT ON;

DECLARE @ErrorNumber int = ERROR_NUMBER()
	,@ErrorSeverity int = ERROR_SEVERITY()
	,@ErrorState int = ERROR_STATE()
	,@ErrorProcedure nvarchar(126) = ERROR_PROCEDURE()
	,@ErrorLine int = ERROR_LINE()
	,@ErrorMessage nvarchar(2048) = ERROR_MESSAGE();

IF @@TRANCOUNT > 0
	ROLLBACK;

IF @ProcID IS NOT NULL
	SET @ErrorProcedure = N'[' + OBJECT_SCHEMA_NAME(@ProcID) + N'].[' + @ErrorProcedure + N']';

IF @ExtraMessage IS NOT NULL
	SET @ErrorMessage = @ErrorMessage + NCHAR(13) + NCHAR(10) + @ExtraMessage;

-- Email alert
DECLARE @mailbody nvarchar(4000) =
	'Error ' + CAST(@ErrorNumber AS varchar(20)) + ' occurred in procedure '
	+ COALESCE(@ErrorProcedure, 'NA') + ' at line ' + CAST(@ErrorLine AS varchar(20))
	+ ' with a severity of ' + CAST(@ErrorSeverity AS varchar(20))
	+ ' and a state of ' + CAST(@ErrorState AS varchar(20))
	+ '.' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
	+ @ErrorMessage;

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'sqladmin'
	,@recipients = 'junk@junk.com'
	,@importance = 'High'
	,@subject = 'Load Exception'
	,@body = @mailbody;

---- write to log
--INSERT INTO dbo.Exceptions
--(
--	ExceptionDate, ErrorNumber, ErrorSeverity, ErrorState
--	,ErrorProcedure, ErrorLine, ErrorMessage
--)
--SELECT CURRENT_TIMESTAMP, @ErrorNumber, @ErrorSeverity, @ErrorState
--	,COALESCE(@ErrorProcedure, N'NA'), @ErrorLine, COALESCE(@ErrorMessage, N'No Message');

RAISERROR
(
	N'Error %d occurred in procedure %s at line %d. %s'
	,@ErrorSeverity
	,@ErrorState
	,@ErrorNumber
	,@ErrorProcedure
	,@ErrorLine
	,@ErrorMessage
);