UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST

create function dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST]
(
  @ID uniqueidentifier
)
returns bit
as
begin
  --Check if the name format function has a spouse field that comes before a constituent field...

  declare @RETVAL bit = 0;

  --Check the built-in functions...

  if exists(select * from dbo.[NAMEFORMATFUNCTION] where [ID] = @ID and [FORMATSQLFUNCTION] in ('UFN_NAMEFORMAT_04', 'UFN_NAMEFORMAT_05', 'UFN_NAMEFORMAT_17', 'UFN_NAMEFORMAT_18', 'UFN_NAMEFORMAT_24'))
    set @RETVAL = 1;
  else
    --Check any custom functions...

    select top 1
      @RETVAL = [NAMEFORMATFIELD].[ISSPOUSEFIELD]
    from dbo.[NAMEFORMATFUNCTION]
    inner join dbo.[NAMEFORMATFUNCTIONDETAIL] on [NAMEFORMATFUNCTIONDETAIL].[NAMEFORMATFUNCTIONID] = [NAMEFORMATFUNCTION].[ID]
    inner join dbo.[NAMEFORMATFIELD] on [NAMEFORMATFIELD].[ID] = [NAMEFORMATFUNCTIONDETAIL].[NAMEFORMATFIELDID]
    where [NAMEFORMATFUNCTION].[ISJOINTFUNCTION] = 1
    and [NAMEFORMATFUNCTION].[ID] = @ID
    and [NAMEFORMATFIELD].[FIELDCODE] <> 0  --ignore user-defined fields

    order by [NAMEFORMATFUNCTIONDETAIL].[SEQUENCE] asc;

  return @RETVAL;
end