UFN_FAF_HOUSEHOLDMEMBERS_BY_HHTEAMID

CREATE function dbo.UFN_FAF_HOUSEHOLDMEMBERS_BY_HHTEAMID(@HHTEAMID uniqueidentifier)
returns @HOUSEHOLDMEMBER TABLE
  (
  TEAMNAME nvarchar(100)
  ,TEAMID uniqueidentifier
  ,keyname nvarchar(100)
  ,name nvarchar(256) NULL
  ,ID uniqueidentifier
  )with execute as caller
as begin

     INSERT INTO @HOUSEHOLDMEMBER
   select  
     TFT.NAME as TEAMNAME,  
     FNTEAM.TEAMFUNDRAISINGTEAMID,
     C.keyname, 
     C.name,
     C.ID 
    from  
     dbo.TEAMFUNDRAISINGTEAMMEMBER TM
     INNER JOIN dbo.TEAMEXTENSION FNTEAM
        ON TM.TEAMFUNDRAISINGTEAMID = FNTEAM.TEAMFUNDRAISINGTEAMID
        AND FNTEAM.TYPECODE = 3 --household

     INNER JOIN dbo.TEAMFUNDRAISINGTEAM TFT
      ON FNTEAM.TEAMFUNDRAISINGTEAMID = TFT.ID
     INNER JOIN dbo.TEAMFUNDRAISER TF
        ON TM.TEAMFUNDRAISERID = TF.ID
     INNER JOIN dbo.CONSTITUENT C
        ON TF.CONSTITUENTID = C.ID
     WHERE FNTEAM.TEAMFUNDRAISINGTEAMID = @HHTEAMID

    return

end