UFN_FAF_GETTEAMHIEARACHY

This function takes teamID as input and returns a list of ALL child teams in all levels

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@TEAMFUNDRAISINGTEAMID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_FAF_GETTEAMHIEARACHY
(
@TEAMFUNDRAISINGTEAMID uniqueidentifier
)
returns @TeamHierarchy TABLE
  (
  teamID uniqueidentifier
  ,ParentTeamID uniqueidentifier
  ,level tinyint
  )with execute as caller

AS
BEGIN
 WITH CTE_TEAM (TEAMID, PARENTTEAMID, LEVEL)
  AS 
  ( SELECT ID, PARENTTEAMID, 0 AS level
  FROM dbo.TEAMFUNDRAISINGTEAM
  WHERE ID = @TEAMFUNDRAISINGTEAMID
  UNION ALL
    SELECT TFT.ID, TFT.parentTEAMID, LEVEL + 1
    FROM dbo.TEAMFUNDRAISINGTEAM TFT
    INNER JOIN CTE_TEAM C
    ON TFT.parentTEAMID = C.TEAMID
  )

INSERT INTO  @TeamHierarchy
SELECT TEAMID, PARENTTEAMID, LEVEL 
FROM CTE_TEAM;     

RETURN 
END