USP_SPONSORSHIPOPPORTUNITY_RESERVE_SELECTION

CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_RESERVE_SELECTION (
    @KEY int,
    @MINSELECTION int,
    @MULTIPLIER int)
as
begin    

declare @COUNTID uniqueidentifier
declare    @SELECTEDCOUNT int = 0
declare @INITIALSUM int = 0
declare @NEWSUM int = 0
declare test_cursor cursor local forward_only for      
    select ID from #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS    

declare @CHANGEAGENTID uniqueidentifier;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

select @INITIALSUM = SUM(INITIALCOUNT) from  #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS
select @NEWSUM = SUM(NEWCOUNT) from  #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS

if @KEY <> 0
 begin
    if @MULTIPLIER * @MINSELECTION < @KEY
     begin  
      if @INITIALSUM - @NEWSUM <= @KEY
       begin
        update #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS
        set NEWCOUNT = INITIALCOUNT
        where INITIALCOUNT > NEWCOUNT
       end
      else
       begin
         open test_cursor;
            fetch next from test_cursor into
             @COUNTID;     
         while (@@FETCH_STATUS = 0) and @SELECTEDCOUNT < @KEY
            begin             
              update #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS
              set NEWCOUNT = NEWCOUNT + @MINSELECTION
              where ID = @COUNTID
              and INITIALCOUNT > NEWCOUNT

              if @@ROWCOUNT <> 0
               begin
                set @SELECTEDCOUNT = @SELECTEDCOUNT+@MINSELECTION
               end    

              fetch next from test_cursor into
              @COUNTID;
            end       
          close test_cursor;  
          deallocate test_cursor;       

          set @KEY = @KEY - @SELECTEDCOUNT
          set @MULTIPLIER = 0
          set @MINSELECTION = 1    

          exec  dbo.USP_SPONSORSHIPOPPORTUNITY_RESERVE_SELECTION @KEY, @MINSELECTION, @MULTIPLIER       
        end              
      end  
     else if @MULTIPLIER * @MINSELECTION > @KEY
      begin
       if @KEY < @MULTIPLIER
        begin
            set @MULTIPLIER = 0
            set @MINSELECTION = 1    

            exec  dbo.USP_SPONSORSHIPOPPORTUNITY_RESERVE_SELECTION @KEY, @MINSELECTION, @MULTIPLIER
        end
       else
        begin
            update #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS
            set NEWCOUNT = FLOOR(@KEY/@MULTIPLIER)

            set @KEY = @KEY - (FLOOR(@KEY/@MULTIPLIER) * @MULTIPLIER)
            set @MULTIPLIER = 0
            set @MINSELECTION = 1

            exec  dbo.USP_SPONSORSHIPOPPORTUNITY_RESERVE_SELECTION @KEY, @MINSELECTION, @MULTIPLIER         
        end
      end 
      else
       begin
         update #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS
         set NEWCOUNT = @MINSELECTION    
       end 
 end    

    return 0
end