UFN_MKTSEGMENTATION_GETUNIQUENAME_2

create function dbo.[UFN_MKTSEGMENTATION_GETUNIQUENAME_2]
(
  @NAME nvarchar(100), -- marketing effort name

  @FORMAT nvarchar(50) -- format for specifying the copy format, default format is 'Copy{0} of {name}'

)
/*
returns a unique name for a mailing by adding a counter. 
*/
returns nvarchar(100)
as
begin

  /* format is like 'Copy{0} of {1}', where {0} represents the counter and {1} represents the name */
  declare @NEWNAME nvarchar(100);
  declare @COPYCOUNT int;

  set @COPYCOUNT = 0;
  set @NEWNAME = replace(@FORMAT, '{0}', ''); -- default 'Copy of {name}'

  set @NEWNAME = replace(@NEWNAME, '{1}', @NAME); -- insert name


  while (select count(1) from dbo.[MKTSEGMENTATION] where [NAME] = @NEWNAME) > 0
  begin
    set @COPYCOUNT = @COPYCOUNT + 1;
    set @NEWNAME = replace(@FORMAT, '{0}', ' (' + cast(@COPYCOUNT as nvarchar(10)) + ')' ); -- insert number'

    set @NEWNAME = replace(@NEWNAME, '{1}', @NAME); -- insert name

  end  

  return @NEWNAME;
end;