UFN_FAF_GROUPPARTICIPANTSLIST

CREATE function dbo.UFN_FAF_GROUPPARTICIPANTSLIST(
    @GROUPID uniqueidentifier = null,
    @EVENTID uniqueidentifier = null,
    @TYPECODE int = NULL --1: team, 2:company, 3: household

)
 returns table    
 as    
 return    
 (    
  select DISTINCT
   TE.EVENTID,
   TFT.NAME as TEAMNAME,    
   TE.TEAMFUNDRAISINGTEAMID as TEAMID,
   C.keyname,   
   C.name,  
   C.ID,
   dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(C.ID) CLIENTUSERID
  from dbo.TEAMFUNDRAISINGTEAMMEMBER TM  
   inner join dbo.TEAMEXTENSION TE
      on TM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID  
            and TE.EVENTID=ISNULL(@EVENTID,TE.EVENTID)
            and TE.TYPECODE=ISNULL(@TYPECODE,TE.TYPECODE)
   inner join dbo.TEAMFUNDRAISINGTEAM TFT
      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  
   where TE.TEAMFUNDRAISINGTEAMID = ISNULL(@GROUPID,TE.TEAMFUNDRAISINGTEAMID)
)