DECLARE @TransactionCountOnEntry INT
	DECLARE @ErrorCode INT

	SELECT	@ErrorCode = @@ERROR
	
	IF @ErrorCode = 0
		BEGIN
		
			SELECT @TransactionCountOnEntry = @@TRANCOUNT

			-- Set BEGIN TRAN (transition) so can roll back if there is a problem
			BEGIN TRANSACTION

			DECLARE @SQL NVARCHAR(MAX)

			SET @SQL =
			N'
			EXECUTE Database.dbo.usp_Setup ''' + @variable 
			'
			EXECUTE sp_executeSQL @SQL

			IF @@TRANCOUNT > @TransactionCountOnEntry
				BEGIN
				   IF @ErrorCode = 0
					  COMMIT TRANSACTION
				   ELSE
					  ROLLBACK TRANSACTION
				END

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.Exceptions
(
	ExceptionId int IDENTITY NOT NULL
		CONSTRAINT PK_Exceptions PRIMARY KEY
	,ExceptionDate datetime NOT NULL
	,ErrorNumber int NOT NULL
	,ErrorSeverity int NOT NULL
	,ErrorState int NOT NULL
	,ErrorProcedure nvarchar(126) NOT NULL
	,ErrorLine int NOT NULL
	,ErrorMessage nvarchar(2048) NOT NULL
)
GO
CREATE PROCEDURE dbo.ExceptionHandler
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;

-- 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 = 'YourProfile'
	,@recipients = 'YourEMail'
	,@importance = 'High'
	,@subject = '<SystemName> 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
);
GO
CREATE PROCEDURE dbo.YourMainProc
AS
SET NOCOUNT ON;

BEGIN TRY

	-- Do your work here

	-- eg 1 Call other procs that do not have error handling
	EXEC YourSubProc1;

	-- eg 2 An explicit transaction
	BEGIN TRAN
		INSERT ....
		UPDATE ...
		etc
	COMMIT

	-- eg 3 SELECTs
	SELECT ...

END TRY
BEGIN CATCH;
	EXEC dbo.ExceptionHandler;
END CATCH;