USP_HOUSEHOLD_REFRESHNAME

          CREATE procedure dbo.USP_HOUSEHOLD_REFRESHNAME
          (
            @TARGETID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier
          )
          as
          begin
            set nocount on;

            declare @HOUSEHOLDID as uniqueidentifier;
            declare @CURRENTDATE as datetime = getdate();

            select 
              @HOUSEHOLDID = GD.ID 
            from 
              dbo.GROUPMEMBER GM 
            inner join 
              dbo.GROUPDATA GD on GD.ID = GM.GROUPID
            where 
              GM.MEMBERID = @TARGETID and 
              GD.GROUPTYPECODE = 0 and -- Group Type Code 0 is for Households

              GD.NAMEFORMATFUNCTIONID is not null;


            if @HOUSEHOLDID is not null
            begin
              declare @HOUSEHOLDNAME as nvarchar(100)

              select 
                @HOUSEHOLDNAME = left(dbo.UFN_NAMEFORMAT_FROMID(COALESCE (GD.NAMEFORMATFUNCTIONID, (select top(1) NAMEFORMATFUNCTIONID from dbo.HOUSEHOLDINFO order by DATEADDED)), GM.MEMBERID), 100)
              from 
                dbo.GROUPDATA GD
              left join 
                dbo.GROUPMEMBER GM on GD.ID = GM.GROUPID and GM.ISPRIMARY <> 0
              where 
                GD.ID = @HOUSEHOLDID

              -- Household name can come back null if the household is dissolved and has no primary member

              if @HOUSEHOLDNAME is not null
              begin
                update
                  dbo.CONSTITUENT
                set
                  KEYNAME = @HOUSEHOLDNAME,
                  CHANGEDBYID = @CHANGEAGENTID, 
                  DATECHANGED = @CURRENTDATE
                where 
                  ID = @HOUSEHOLDID;
              end
            end

         end
         return 0;