UFN_FAF_COMPANYMEMBERLIST_BY_COMPANYID

CREATE function dbo.UFN_FAF_COMPANYMEMBERLIST_BY_COMPANYID
(
  @COMPANYID uniqueidentifier = NULL
)
returns @COMPANYMEMBER TABLE
  (
  TEAMNAME nvarchar(100)
  ,TEAMID uniqueidentifier
  ,keyname nvarchar(100)
  ,name nvarchar(256) NULL
  ,ID uniqueidentifier
  )with execute as caller
AS
BEGIN

  INSERT INTO @COMPANYMEMBER
  select  
    TFT.NAME,  
    TFT.ID,
    C.keyname, 
    C.name,
    C.ID 
  from  dbo.UFN_FAF_GETTEAMHIEARACHY(@COMPANYID) TH
  INNER JOIN dbo.TEAMFUNDRAISINGTEAM TFT
    ON TH.TEAMID = TFT.ID
  INNER JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER TM
    ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
  INNER JOIN dbo.TEAMFUNDRAISER TF
    ON TM.TEAMFUNDRAISERID = TF.ID
  INNER JOIN dbo.CONSTITUENT C
    ON TF.CONSTITUENTID = C.ID

  RETURN
END