USP_DATALIST_MKTSOURCECODEINCREMENTSEGMENTCODE

create procedure dbo.[USP_DATALIST_MKTSOURCECODEINCREMENTSEGMENTCODE]
(
  @PARTDEFINITIONVALUESID uniqueidentifier = null,
  @NUMBEROFCODESTORETURN integer = 1
)
as
  set nocount on;

  declare @AUTOINCREMENTEDCODES nvarchar(max);
  declare @NEXTCODE nvarchar(10);
  declare @CODESINCREMENTED integer;
  declare @CODEEXISTS bit;
  declare @FORMAT nvarchar(10);

  select
    @FORMAT = [MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]
  from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID]
  where [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = @PARTDEFINITIONVALUESID;

  /* Get next code in the sequence */
  set @AUTOINCREMENTEDCODES = ''
  select @NEXTCODE = dbo.[UFN_MKTSOURCECODE_AUTOINCREMENTCODE](@PARTDEFINITIONVALUESID, 1);
  set @AUTOINCREMENTEDCODES = @NEXTCODE;
  set @CODESINCREMENTED = 1;
  set @CODEEXISTS = 0;

  /* This to make sure the maximum number of segments we check for unique codes is less than the total number of segments */
  declare @ROWCOUNT integer;
  declare @LOOPCOUNT integer;
  declare @MAXNUMBEROFCHECKSREACHED bit;
  set @LOOPCOUNT = 0;
  select @ROWCOUNT = count([CODE]) from dbo.[MKTSEGMENT] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
  set @MAXNUMBEROFCHECKSREACHED = 0;

  while (@CODESINCREMENTED < @NUMBEROFCODESTORETURN)
  begin
    set @NEXTCODE = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTCODE)
    set @CODEEXISTS = case when exists(select 1 from dbo.[MKTSEGMENT] where [CODE] = @NEXTCODE) then 1 else 0 end;

    /* To find next code, we use the last code found as a starting point */
    while (@CODEEXISTS = 1 and @MAXNUMBEROFCHECKSREACHED = 0)
    begin
      set @LOOPCOUNT = 0;
      set @NEXTCODE = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTCODE)
      set @CODEEXISTS = case when exists(select 1 from dbo.[MKTSEGMENT] where [CODE] = @NEXTCODE) then 1 else 0 end;

      set @LOOPCOUNT += 1;
      if @LOOPCOUNT >= @ROWCOUNT
        set @MAXNUMBEROFCHECKSREACHED = 1;
    end

    set @AUTOINCREMENTEDCODES = @AUTOINCREMENTEDCODES + ',' + @NEXTCODE;
    set @CODESINCREMENTED += 1;
  end

  select @AUTOINCREMENTEDCODES;

  return 0;