UFN_MKTLISTLAYOUT_GETUNIQUENAME

CREATE function dbo.[UFN_MKTLISTLAYOUT_GETUNIQUENAME]
(
  @ID uniqueidentifier,
  @NAME nvarchar(50)
)
/*
returns a unique name for the layout by appending a counter.  Used in the update/insert triggers
for the MKTLISTLAYOUT table
*/
returns nvarchar(50)
as
begin
  declare @NEWNAME nvarchar(255);
  declare @TRIMNAME nvarchar(50);
  declare @COPYCOUNT int;
  declare @INSTANCECOUNT int;

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

  set @INSTANCECOUNT = (select count(*) from [dbo].[MKTLISTLAYOUT] L where L.[NAME]=@NEWNAME and L.[ID]<>@ID);
  while @INSTANCECOUNT > 0
  begin
    set @COPYCOUNT=@COPYCOUNT+1;
    set @NEWNAME = @TRIMNAME + ' (' + cast(@COPYCOUNT as nvarchar(20)) + ')';
    while len(@NEWNAME)>50 and len(@TRIMNAME)>1
    begin
      set @TRIMNAME = substring(@TRIMNAME,1,LEN(@TRIMNAME)-1);
      set @NEWNAME = @TRIMNAME + ' (' + cast(@copycount as nvarchar(20)) + ')';
    end;
    set @instancecount = (select count(*) from [dbo].[MKTLISTLAYOUT] L where L.[NAME]=@NEWNAME and L.[ID]<>@ID);
  end;

  return @NEWNAME;
end;