USP_MKTCOMMON_GETUNIQUENAME

CREATE procedure dbo.[USP_MKTCOMMON_GETUNIQUENAME]
(
  @NAME nvarchar(4000) output, -- name of item to copy

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

  @TABLENAME nvarchar(250), -- name of the sql table containing the item to copy

  @NAMECOLUMN nvarchar(250), -- name of the name column in the sql table of the item to copy

  @COPYCOUNT bigint = 0 output --the number appended to the name

)
/*
returns a unique name for an object by adding a counter.
*/
as
begin
  set nocount on;

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

  /* get the max length of the name column */
  declare @COLMAXLENGTH integer;
  declare @MAXLENGTH integer;

  begin try
    select @COLMAXLENGTH = CHARACTER_MAXIMUM_LENGTH
    from information_schema.columns
    where TABLE_NAME = @TABLENAME and COLUMN_NAME = @NAMECOLUMN;

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

    set @MAXLENGTH = @COLMAXLENGTH - len(replace(replace(@FORMAT, '{0}', ''), '{1}', ''));  -- don't include brackets in max length calculation


    /* name is greater than the maximum available length, so trim name part */
    if len(@NAME) > @MAXLENGTH  
      set @NAME = substring(@NAME, 1, @MAXLENGTH);

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


    /* get the number of items with the desired name */
    declare @NAMECOUNT int;
    declare @SQL nvarchar(max) = 'select @NAMECOUNT = count(1) from dbo.[' + @TABLENAME + '] where [' + @NAMECOLUMN + '] = @NEWNAME;'
    declare @PARAMS nvarchar(max) = '@NEWNAME nvarchar(4000), @NAMECOUNT int output';
    exec sp_executesql @SQL, @PARAMS, @NEWNAME = @NEWNAME, @NAMECOUNT = @NAMECOUNT output;

    while @NAMECOUNT > 0
    begin
      set @COPYCOUNT = @COPYCOUNT + 1;
      set @NEWNAME = replace(@FORMAT, '{1}', ' (' + cast(@COPYCOUNT as nvarchar(10)) + ')' ); -- insert number'


      set @MAXLENGTH = @COLMAXLENGTH - len(replace(replace(@FORMAT, '{0}', ''), '{1}', ' (' + cast(@COPYCOUNT as nvarchar(10)) + ')'));  -- don't include brackets in max length calculation


      /* name is greater than the maximum available length, so trim name part */
      if len(@NAME) > @MAXLENGTH
        set @NAME = substring(@NAME, 1, @MAXLENGTH);

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


      exec sp_executesql @SQL, @PARAMS, @NEWNAME = @NEWNAME, @NAMECOUNT = @NAMECOUNT output;
    end  

    set @NAME = @NEWNAME;

  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;

end;