UFN_NAMEFORMAT_FROMID_INTERNAL

CREATE function dbo.UFN_NAMEFORMAT_FROMID_INTERNAL
(
  @NAMEFORMATFUNCTIONID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @EXCLUDESPOUSEINFO bit
)
returns nvarchar(700)
with execute as caller
as begin
  declare @NAME nvarchar(700);

  if @EXCLUDESPOUSEINFO = 0 
    begin
      select
        @NAME = dbo.UFN_BUILDNAMEFORMAT(
          @NAMEFORMATFUNCTIONID,
          CONSTITUENT.ID,
          CONSTITUENT.KEYNAME,
          CONSTITUENT.FIRSTNAME,
          CONSTITUENT.MIDDLENAME,
          (select DESCRIPTION from dbo.TITLECODE where ID = CONSTITUENT.TITLECODEID),
          (select DESCRIPTION from dbo.SUFFIXCODE where ID = CONSTITUENT.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.CONSTITUENT 
      left outer join dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
      left outer join dbo.DECEASEDCONSTITUENT as SPOUSEDECEASEDCONSTIT on SPOUSEDECEASEDCONSTIT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
      left outer join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID and SPOUSEDECEASEDCONSTIT.ID is null and SPOUSE.ISINACTIVE = 0
      where CONSTITUENT.ID = @CONSTITUENTID;
    end
  else
    begin
      select
        @NAME = dbo.UFN_BUILDNAMEFORMAT(
          @NAMEFORMATFUNCTIONID,
          CONSTITUENT.ID,
          CONSTITUENT.KEYNAME,
          CONSTITUENT.FIRSTNAME,
          CONSTITUENT.MIDDLENAME,
          (select DESCRIPTION from dbo.TITLECODE where ID = CONSTITUENT.TITLECODEID),
          (select DESCRIPTION from dbo.SUFFIXCODE where ID = CONSTITUENT.SUFFIXCODEID),
          '',
          '',
          '',
          '',
          ''
        )
      from dbo.CONSTITUENT
      where CONSTITUENT.ID = @CONSTITUENTID;
    end

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

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

  return @NAME;
end;