Cacher is the code snippet organizer for pro developers

Create PROCEDURE [dbo].[SPWithTransaction] @mgrId VARCHAR(50)
	,@groupId INT
AS
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION

		--Your Transaction Here

		-- if successful - COMMIT the work
		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;

		DECLARE @ErrorNumber INT = ERROR_NUMBER();
		DECLARE @ErrorLine INT = ERROR_LINE();
		DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
		DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
		DECLARE @ErrorState INT = ERROR_STATE();
		PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
		PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
		RAISERROR (
				@ErrorMessage
				,@ErrorSeverity
				,@ErrorState
				);
		--THROW;
	END CATCH
END