Hi Mehram,
I have verified the query. It has no issue.
Loop is working properly.
Mehram says:
SET @AlreadyPaid = @AlreadyPaid - @BalanceAmt
You need to just verify the above condition.
declare @AlreadyPaid DECIMAL
Set @AlreadyPaid=2070958.00
DECLARE @Counter INT, @TotalCount INT, @InvoiceID INT, @BalanceAmt Numeric(18,2)
SET @Counter = 1
SET @TotalCount = (SELECT COUNT(*) FROM Invoice)
If @AlreadyPaid > 0
WHILE (@Counter <= @TotalCount) and @AlreadyPaid>0
BEGIN
SET @InvoiceID = (SELECT InvoiceID FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM Invoice) t WHERE t.RowId = @Counter)
SET @BalanceAmt = (SELECT BalanceAmt FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM Invoice) t WHERE t.RowId = @Counter)
UPDATE Invoice
SET AlreadyPaid=Case When @AlreadyPaid > @BalanceAmt then @BalanceAmt else @AlreadyPaid end
WHERE InvoiceID = @InvoiceID and CompanyID=@CompanyID
SET @AlreadyPaid = @AlreadyPaid - @BalanceAmt
SET @Counter = @Counter + 1
CONTINUE;
END
For more details refer below link.
