OrgTre

ALTER FUNCTION [dbo].[OrgTre] (@orgid INT)
RETURNS @returnTable TABLE (
	orgid INT
	,eier INT
	,nivaa INT
	,tekst NVARCHAR(500)
	)
AS
BEGIN
	INSERT INTO @returnTable
	SELECT orgid
		,0
		,0
		,tekst
	FROM tblOrg
	WHERE OrgID = @orgid;

	WITH Tre
	AS (
		SELECT OrgID
			,Tekst
			,1 AS Nivaa
			,Eier
			,CAST((tekst) AS NVARCHAR(max)) AS [Path]
		FROM tblOrg
		WHERE Eier = @orgid
		
		UNION ALL
		
		--Recursive member
		SELECT t.OrgID
			,t.Tekst
			,Nivaa + 1 AS Nivaa
			,t.Eier
			,CAST((a.path + '/' + t.tekst) AS NVARCHAR(max)) AS [Path]
		FROM tblOrg t
		JOIN Tre AS a ON t.Eier = a.OrgID
		)
	INSERT INTO @returnTable
	SELECT tre.OrgID
		,tre.Eier
		,tre.Nivaa
		,tre.Tekst
	FROM Tre

	RETURN
END