UFN_REVENUERECEIPT_GETNEWUNIQUERECEIPTNUMBERS

CREATE function dbo.UFN_REVENUERECEIPT_GETNEWUNIQUERECEIPTNUMBERS(    
    @STARTINGRECEIPTNUMBER int = 0,
    @COUNT int = 0,
    @RECEIPTSTACKINFOID uniqueidentifier = null)
returns @RESULTS table
(
OLDRECEIPTNUM int,
NEWRECEIPTNUM int
)
as
begin

    if @COUNT = 0 return

    declare @DUPLICATES TABLE (ROWNUM int IDENTITY(0,1), RECEIPTNUM int)
    declare @NUMBEROFDUPLICATES as int
    declare @STARTINGNUMBER as int
    declare @idx as int

    insert into @DUPLICATES
    select RECEIPTNUMBER from dbo.REVENUERECEIPT RR 
    where RECEIPTSTACKINFOID = @RECEIPTSTACKINFOID and RR.RECEIPTNUMBER between @STARTINGRECEIPTNUMBER and (@STARTINGRECEIPTNUMBER+@COUNT-1) 
    order by RR.RECEIPTNUMBER

    select @NUMBEROFDUPLICATES = count(*) from @DUPLICATES

    set @idx = 0
    declare @NEWNUM int
    set @NEWNUM = @STARTINGRECEIPTNUMBER+@COUNT

  declare @ISMAX bit
  set @ISMAX = 0
  if not exists (select max(RECEIPTNUMBER) from dbo.REVENUERECEIPT where RECEIPTNUMBER>@NEWNUM and RECEIPTSTACKINFOID = @RECEIPTSTACKINFOID)
  set @ISMAX = 1

  if(@ISMAX = 0) 
  begin
      while @idx < @NUMBEROFDUPLICATES
          begin
        if exists (select * from dbo.REVENUERECEIPT where RECEIPTNUMBER = @NEWNUM and RECEIPTSTACKINFOID = @RECEIPTSTACKINFOID)
          with RRCTE As
          (
          select RECEIPTNUMBER as RECEIPTNUMBER,RECEIPTSTACKINFOID from dbo.REVENUERECEIPT where RECEIPTSTACKINFOID = @RECEIPTSTACKINFOID and RECEIPTNUMBER >= @NEWNUM 
          )
          select @NEWNUM = min(RRCTE.RECEIPTNUMBER)+1 from RRCTE
          left join RRCTE RRCTE2 on RRCTE2.RECEIPTNUMBER = RRCTE.RECEIPTNUMBER +1
          where RRCTE2.RECEIPTNUMBER is null

            insert @RESULTS (OLDRECEIPTNUM,NEWRECEIPTNUM) select top 1 RECEIPTNUM, @NEWNUM from @DUPLICATES where ROWNUM = @idx;
            set @NEWNUM = @NEWNUM+1;
            set @idx = @idx+1;
          end
  end
  else    
    begin
      while @idx < @NUMBEROFDUPLICATES
            begin   
              insert @RESULTS (OLDRECEIPTNUM,NEWRECEIPTNUM) select top 1 RECEIPTNUM, @NEWNUM from @DUPLICATES where ROWNUM = @idx;
              set @NEWNUM = @NEWNUM+1;
              set @idx = @idx+1;
            end
    end

    return

end