Delphi procedure -> MS SQL stored procedure

[Squall_FF8 1]

Hey guys,
I have written a procedure in Delphi, but would like to port it to MS SQL. Unfortunately I'm new to MSSQL so If you have time to lift a hand, even with parts of it - that would be of great help!!!

The structure: 3 tables
- Contract - with contract info. It has Date_Sign, Date_Start, Date_Stop, MonthlyTax
- Payment -  keep records of paid taxes for each Month/Contract. It has DatePayment, Year, Month, Amount
- Annex - keep track when the tax has been changed. It has Date and Amount

I'm trying to create a procedure in MSSQL that takes ContractID and returns
- Months - number of months without payment
- Owes- sum of all taxes that hasn't been paid
 

procedure TfmContractEdit.BuildChrono;
  var y, m, d: word;
      d1, d2: tDate;
      pay: Currency;  // keeps track of what current tax that needs to be paid
begin
  if deStart.Enabled then d1 := deStart.Date
                     else d1 := deSign.Date;
  d1 := RecodeDay(d1, 1);

  if deStop.Enabled then d2 := deStop.Date
                    else d2 := Date;
  d2 := RecodeDay(d2, 1);

  DM.Qry.Open(format(sql_GetAnnex, [ID]));  // ID is ContractID
  DM.Qry2.Open(format(sql_GetPaymentTax, [ID]));
  pay := cuMonthTax.Value;  // Current Contract tax
  var Sum: Currency := 0;
  var MonthDebt: integer := 0;
  
  while d1 < d2 do begin
    if (not DM.Qry.Eof) and (d1 >= DM.Qry.FieldByName('Date').AsDateTime) then begin
      pay := DM.Qry.FieldByName('Amount').AsCurrency;
      DM.Qry.Next;
    end;

    DecodeDate(d1, y, m, d);
    var v: variant := DM.Qry2.Lookup('Year;Month',VarArrayOf([y, m]), 'MonthlyTax');
    if VarIsNull(v) then begin 
      Sum := Sum + pay; 
      inc(MonthDebt); 
    end;
    d1 := IncMonth(d1);
  end;

// Returns Sum, MonthDebt

 

Edited 18 hours ago by Squall_FF8

[Squall_FF8 1]

The hardest part for me is - how to do Next on SQL query inside procedure.
Also is it possible to do Lookup, without re-running the same SQL statement each iteration

Edited 18 hours ago by Squall_FF8

[weirdo12 28]

You should provide the values of sql_GetAnnex and sql_GetPaymentTax.