UFN_MKTSOURCECODE_GETUNIQUENAME

create function dbo.[UFN_MKTSOURCECODE_GETUNIQUENAME]
(
  @ID uniqueidentifier,
  @NAME nvarchar(100),
  @PREFIX nvarchar(10)=null
)
/*
returns a unique name for a source code by adding a counter. 
*/
returns nvarchar(100)
as
begin
  declare @NEWNAME nvarchar(255);
  declare @TRIMNAME nvarchar(100);
  declare @COPYCOUNT int;
  declare @INSTANCECOUNT int;

  if @PREFIX is null 
    set @PREFIX = 'Copy';

  set @COPYCOUNT = 0;
  set @NEWNAME = @NAME;
  set @TRIMNAME = @NAME;

  set @INSTANCECOUNT = (select count(*) from [dbo].[MKTSOURCECODE] L where L.[NAME]=@NEWNAME);
  while @INSTANCECOUNT > 0
  begin
    set @COPYCOUNT=@COPYCOUNT+1;
    if @COPYCOUNT>1
      set @NEWNAME = @PREFIX + ' (' + cast(@COPYCOUNT as nvarchar(20)) + ') of ' + @TRIMNAME
    else
      set @NEWNAME = @PREFIX + ' of ' + @TRIMNAME;

    while len(@NEWNAME)>100 and len(@TRIMNAME)>1
    begin
      set @TRIMNAME = substring(@TRIMNAME,1,LEN(@TRIMNAME)-1);
      if @COPYCOUNT>1
        set @NEWNAME = @PREFIX + ' (' + cast(@COPYCOUNT as nvarchar(20)) + ') of ' + @TRIMNAME
      else
        set @NEWNAME = @PREFIX + ' of ' + @TRIMNAME;
    end;

    set @instancecount = (select count(*) from [dbo].[MKTSOURCECODE] L where L.[NAME]=@NEWNAME and L.[ID]<>@ID);
  end;
  return @NEWNAME;
end;