[Solved] While Loop does not end in SQL Server

Mehram
 
on Jun 27, 2021 10:15 PM
476 Views

Sir,

Why this loop is not getting end. 

Please guide

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
    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)
     
    BEGIN
        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
    END

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 28, 2021 02:29 AM

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.

DO WHILE and WHILE loop in SQL Server