SQL Server generates string for recursive menu parents

menuHeritage.sql

CREATE FUNCTION [dbo].[f_menuHeritage]
(
	@menuId INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @result VARCHAR(1000);
	SET @result = '';
	
	WITH menuParents (parentMenuId, menuId, name, level) AS (
		-- Anchor member
		SELECT	 parentMenuId
				,menuId
				,name
				,0 AS level
		FROM	system.menu
		WHERE	menuId = @menuId
		
		UNION ALL
		
		-- Get the next level of hirarchy recursion
		SELECT	 M.parentMenuId
				,M.menuId
				,M.name
				,level + 1 AS level
		FROM	menuParents MP
		JOIN	system.menu M
			ON	M.menuId = MP.parentMenuId
	)
	
	SELECT	@result =
				CASE @result
				WHEN '' THEN name
				ELSE @result + ' > ' + name
				END
	FROM	menuParents
	ORDER BY RANK () OVER (ORDER BY level DESC)
	
	RETURN @result

END