UFN_NUMBERTEAMMEMBERS_GROUP

CREATE function [dbo].[UFN_NUMBERTEAMMEMBERS_GROUP]
(
    @TEAMFUNDRAISINGTEAMID as uniqueidentifier
)
returns TABLE
as
return

   With [All] (TeamID)
  As 
  (        
    select  PL.TEAMID 
    from dbo.UFN_FAF_GROUPPARTICIPANTSLIST(NULL, (select TX.EVENTID from TEAMEXTENSION TX where TX.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID) ,NULL) PL
    join dbo.REGISTRANT R ON PL.ID = R.CONSTITUENTID and PL.EVENTID = R.EVENTID
    join dbo.UFN_REGISTRANT_GETFAFROLE((select TX.EVENTID from TEAMEXTENSION TX where TX.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID), null) PARTICIPANTROLE on PARTICIPANTROLE.REGISTRANTID = R.ID
    join dbo.REGISTRANTEXTENSION RX ON R.ID = RX.REGISTRANTID
    left join dbo.TEAMFUNDRAISINGTEAM TFT on TFT.ID = PL.TEAMID
    left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC ON TFTC.CONSTITUENTID = PL.ID and TFTC.TEAMFUNDRAISINGTEAMID = PL.TEAMID
    left join dbo.EMAILADDRESS EA on PL.ID = EA.CONSTITUENTID and EA.ISPRIMARY = 1
    left join dbo.PHONE P on PL.ID = P.CONSTITUENTID and P.ISPRIMARY = 1
    Where PL.TEAMID = @TEAMFUNDRAISINGTEAMID  

    Union all

    select TFT.ID
    from dbo.TEAMFUNDRAISINGTEAM TFT
    join dbo.TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
    join dbo.CONSTITUENT C on TX.TEAMCONSTITUENTID = C.ID
    where TFT.PARENTTEAMID = @TEAMFUNDRAISINGTEAMID  
  )

  select TeamID
  from [All]