UFN_MKTCOMMUNICATIONTEMPLATEKPI_GETNAME

create function dbo.[UFN_MKTCOMMUNICATIONTEMPLATEKPI_GETNAME]
(
  @ID uniqueidentifier
)
returns nvarchar(100)
as
begin
  declare @KPINAMESUFFIX nvarchar(50) = ' for Marketing Effort';
  declare @MEMBERSHIPKPINAMESUFFIX nvarchar(50) = ' for Membership Renewal Effort';

  return (
    select
      case when [KPICATALOG].[NAME] like '%' + @KPINAMESUFFIX
           then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@KPINAMESUFFIX))
           when [KPICATALOG].[NAME] like '%' + @MEMBERSHIPKPINAMESUFFIX
           then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@MEMBERSHIPKPINAMESUFFIX))
           else [KPICATALOG].[NAME] 
      end as [NAME]
    from dbo.[KPICATALOG]
    where [ID] = @ID
  );
end