UFN_MKTCOMMUNICATIONNAMESCHEME_GETCURRENTNAMESCHEMETEXT

CREATE function dbo.[UFN_MKTCOMMUNICATIONNAMESCHEME_GETCURRENTNAMESCHEMETEXT]
(
  @MKTCOMMUNICATIONNAMESCHEMEID uniqueidentifier
)
returns nvarchar(max)
as
begin

  declare @RESULT nvarchar(max);

  /* returns a name format with parts separated by the separatortype like '[<separator>] [<separatortype>] [<separator>]' */
  select 
    @RESULT = coalesce(@RESULT + '', '') + '[' + [NAMEPARTTYPE] + ']' + 
    case [SEPARATORTYPECODE] 
      when 1 then ' '
      when 2 then '-'
      when 3 then '/'
      when 4 then '.'
      when 5 then ','
      when 6 then '_'
      when 7 then '*'
      when 8 then ':'
      else '' end
  from dbo.[MKTCOMMUNICATIONNAMESCHEMEPART]
  where [MKTCOMMUNICATIONNAMESCHEMEID] = @MKTCOMMUNICATIONNAMESCHEMEID
  order by [SEQUENCE];

  return @RESULT;
end