UFN_FAF_TEAMMEMBERLIST_BY_TEAMID

CREATE function dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(@TEAMID uniqueidentifier)
 returns table   
 as    

 return    
 (    
  select    
   TFT.NAME as TEAMNAME,    
   TE.TEAMFUNDRAISINGTEAMID as TEAMID,  
   C.keyname,   
   C.name,  
   C.ID,
   TE.TYPECODE
  from dbo.TEAMFUNDRAISINGTEAMMEMBER TM  
   inner join dbo.TEAMEXTENSION TE
      on TM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID  
      and TE.TYPECODE = 1  --team  

   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 = @TEAMID

   union  --Also retrieve household leaders when team has households  


   select   
   TFT.NAME as TEAMNAME,    
   TFT.ID as TEAMID,
   C.keyname,   
   C.name,  
   C.ID,
   TE2.TYPECODE
  from TEAMFUNDRAISINGTEAM TFT  
  inner join TEAMEXTENSION TE  
      on TFT.ID = TE.TEAMFUNDRAISINGTEAMID  
      and TE.TYPECODE = 1  --team

  inner join TEAMFUNDRAISINGTEAM TFT2
      ON TFT2.PARENTTEAMID = TFT.ID  
  inner join dbo.TEAMEXTENSION TE2  
      on TFT2.ID = TE2.TEAMFUNDRAISINGTEAMID
      and TE2.TYPECODE = 3   --household

  inner join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  
      on TFT2.ID = TC.TEAMFUNDRAISINGTEAMID  
  inner join dbo.CONSTITUENT C  
      on TC.CONSTITUENTID = C.ID    
  where  TFT.ID = @TEAMID
)