UFN_BATCHSPONSORSHIPCONSTITUENT_NAMEFORMAT_FROMID

            create function dbo.UFN_BATCHSPONSORSHIPCONSTITUENT_NAMEFORMAT_FROMID(
                @NAMEFORMATFUNCTIONID uniqueidentifier,
                @BATCHSPONSORSHIPCONSTITUENTID uniqueidentifier
            ) returns nvarchar(700)
            with execute as caller
            as begin
                declare @NAME nvarchar(700);

                select
                    @NAME = dbo.UFN_BUILDNAMEFORMAT(
                        @NAMEFORMATFUNCTIONID,
                        coalesce(CONSTITUENT.ID, BATCHSPONSORSHIPCONSTITUENT.ID),
                        coalesce(CONSTITUENT.KEYNAME, BATCHSPONSORSHIPCONSTITUENT.KEYNAME),
                        coalesce(CONSTITUENT.FIRSTNAME, BATCHSPONSORSHIPCONSTITUENT.FIRSTNAME),
                        coalesce(CONSTITUENT.MIDDLENAME, BATCHSPONSORSHIPCONSTITUENT.MIDDLENAME),
                        (select DESCRIPTION from dbo.TITLECODE where ID = coalesce(CONSTITUENT.TITLECODEID, BATCHSPONSORSHIPCONSTITUENT.TITLECODEID)),
                        (select DESCRIPTION from dbo.SUFFIXCODE where ID = coalesce(CONSTITUENT.SUFFIXCODEID, BATCHSPONSORSHIPCONSTITUENT.SUFFIXCODEID)),
                        coalesce(EXISTING_SPOUSE.KEYNAME, NEW_EXISTING_SPOUSE.KEYNAME, NEW_SPOUSE.KEYNAME),
                        coalesce(EXISTING_SPOUSE.FIRSTNAME, NEW_EXISTING_SPOUSE.FIRSTNAME, NEW_SPOUSE.FIRSTNAME),
                        coalesce(EXISTING_SPOUSE.MIDDLENAME, NEW_EXISTING_SPOUSE.MIDDLENAME, NEW_SPOUSE.MIDDLENAME),
                        (select DESCRIPTION from dbo.TITLECODE where ID = coalesce(EXISTING_SPOUSE.TITLECODEID, NEW_EXISTING_SPOUSE.TITLECODEID, NEW_SPOUSE.TITLECODEID)),
                        (select DESCRIPTION from dbo.SUFFIXCODE where ID = coalesce(EXISTING_SPOUSE.SUFFIXCODEID, NEW_EXISTING_SPOUSE.SUFFIXCODEID, NEW_SPOUSE.SUFFIXCODEID))
                    )
                from dbo.BATCHSPONSORSHIPCONSTITUENT
                    left join dbo.CONSTITUENT on BATCHSPONSORSHIPCONSTITUENT.EXISTINGCONSTITUENTID = CONSTITUENT.ID    
                    left join dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and RELATIONSHIP.ISSPOUSE <> 0
                    left join dbo.CONSTITUENT as EXISTING_SPOUSE on EXISTING_SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                    left join dbo.BATCHSPONSORSHIPCONSTITUENTRELATION on BATCHSPONSORSHIPCONSTITUENT.ID = BATCHSPONSORSHIPCONSTITUENTRELATION.CONSTITUENTID and BATCHSPONSORSHIPCONSTITUENTRELATION.ISSPOUSE <> 0
                    left join dbo.BATCHSPONSORSHIPCONSTITUENT as NEW_SPOUSE on BATCHSPONSORSHIPCONSTITUENTRELATION.RELATIONID = NEW_SPOUSE.ID
                    left join dbo.CONSTITUENT as NEW_EXISTING_SPOUSE on NEW_SPOUSE.EXISTINGCONSTITUENTID = NEW_EXISTING_SPOUSE.ID
                where
                    BATCHSPONSORSHIPCONSTITUENT.ID = @BATCHSPONSORSHIPCONSTITUENTID;

                -- CONDBREAK is not currently handled anywhere, so just remove it, at least for now.

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

                return @NAME;
            end;