USP_AUCTIONITEMBATCH_GETHOUSEHOLDNAMEORID

            create procedure dbo.USP_AUCTIONITEMBATCH_GETHOUSEHOLDNAMEORID
            (
                @BATCHCONSTITUENTID uniqueidentifier,
                @BATCHSPOUSEID uniqueidentifier,
                @HOUSEHOLDID uniqueidentifier output,
                @HOUSEHOLDNAME nvarchar(100) output,
                @USEHOUSEHOLDID bit output -- Indicates whether the ID or name should be used

            )
            as
                set nocount on

                declare @CURRENTDATEEARLIESTTIME datetime;
                set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                -- Determine if the spouse already belongs to a household.  If so, use that one.  Otherwise, use a new household

                -- whose name is generated with the household name format function.

                select
                    @HOUSEHOLDID = GM.GROUPID,
                    @HOUSEHOLDNAME = H.NAME
                from dbo.BATCHAUCTIONITEMCONSTITUENT BRC
                inner join dbo.GROUPMEMBER GM on BRC.EXISTINGCONSTITUENTID = GM.MEMBERID
                inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
                inner join dbo.CONSTITUENT H on GM.GROUPID = H.ID
                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                where
                    GD.GROUPTYPECODE = 0 and -- Group is a household

                    BRC.ID = @BATCHSPOUSEID    
                    -- Constituent is current member

                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATEEARLIESTTIME))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                        or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO >= @CURRENTDATEEARLIESTTIME));

                if @HOUSEHOLDID is not null
                    set @USEHOUSEHOLDID = 1
                else
                begin
                    set @USEHOUSEHOLDID = 0

                    -- Generate household name

                    declare @NAMEFORMATFUNCTIONID uniqueidentifier;
                    select top(1) @NAMEFORMATFUNCTIONID = NAMEFORMATFUNCTIONID from dbo.HOUSEHOLDINFO order by DATEADDED;

                    select
                    @HOUSEHOLDNAME = dbo.UFN_BUILDNAMEFORMAT(
                        @NAMEFORMATFUNCTIONID,
                        BRC.ID,
                        BRC.KEYNAME,
                        BRC.FIRSTNAME,
                        BRC.MIDDLENAME,
                        (select DESCRIPTION from dbo.TITLECODE where ID = BRC.TITLECODEID),
                        (select DESCRIPTION from dbo.SUFFIXCODE where ID = BRC.SUFFIXCODEID),
                        SPOUSE.KEYNAME,
                        SPOUSE.FIRSTNAME,
                        SPOUSE.MIDDLENAME,
                        (select DESCRIPTION from dbo.TITLECODE where ID = SPOUSE.TITLECODEID),
                        (select DESCRIPTION from dbo.SUFFIXCODE where ID = SPOUSE.SUFFIXCODEID)
                    )
                    from
                        dbo.BATCHAUCTIONITEMCONSTITUENT BRC
                        left outer join dbo.BATCHAUCTIONITEMCONSTITUENTRELATION BRCR on BRC.ID = BRCR.CONSTITUENTID
                        left outer join dbo.BATCHAUCTIONITEMCONSTITUENT SPOUSE on SPOUSE.ID = BRCR.RELATIONID
                    where
                        BRC.ID = @BATCHCONSTITUENTID and
                        SPOUSE.ID = @BATCHSPOUSEID;

                    set @HOUSEHOLDNAME = replace(@HOUSEHOLDNAME, N'{CONDBREAK}', N' ');
                end