While Loop in SQL not working

Question

I need help in understanding why is the loop below not working. It only inserts the first record it then stops.

DECLARE 
@NEW_BALANCE            FLOAT
, @CUR_NEW_BALANCE      FLOAT
, @OLD_BALANCE          FLOAT
, @CUR_OLD_BALANCE      FLOAT
, @INITIAL_BALANCE      FLOAT
, @RATE                 FLOAT
, @MONTHLY_RATE         FLOAT
, @MONTHLY_INTEREST     FLOAT
, @PAYMENT              FLOAT



SELECT @RATE    = INTEREST
    FROM ACCOUNTS WHERE ACCOUNT = 'AMEXDELTA'

SET @MONTHLY_RATE               = (@RATE / 12) / 100

SELECT TOP 1 @CUR_NEW_BALANCE       = NEW_BALANCE
             , @CUR_OLD_BALANCE     = OLD_BALANCE
FROM PAYMENTS ORDER BY ID DESC

SELECT @CUR_NEW_BALANCE AS NEW_BALANCE
       , @CUR_OLD_BALANCE AS OLD_BALANCE

WHILE @NEW_BALANCE > 0
BEGIN

    SET @MONTHLY_INTEREST               = (@CUR_OLD_BALANCE * @MONTHLY_RATE)

    SET @OLD_BALANCE                    = (@CUR_NEW_BALANCE - @PAYMENT)

    SET @NEW_BALANCE                    = (@MONTHLY_INTEREST + @OLD_BALANCE)


    INSERT INTO PAYMENTS(PAYMENT, MONTHLY_INTEREST, OLD_BALANCE, NEW_BALANCE)
        SELECT @PAYMENT, @MONTHLY_INTEREST, @OLD_BALANCE, @NEW_BALANCE


END

Below is the current data in the table PAYMENTS:

Old_Balance | New_Balance
------------|------------
2845.8      | 2845.8

Below is the current date in table ACCOUNTS

Interest
--------
15.24

@MartinSmith I put it back. I didn't want to post without thoroughly checking. After re-reading, I didn't understand why it even worked the first time... so I pulled it back. — CJBS
– CJBS, Commented Feb 1, 2014 at 0:58
@CJBS Yes the question must be wrong. The while is never true. So it is executed 0 times not 1. — Martin Smith
– Martin Smith, Commented Feb 1, 2014 at 0:59
I remove the loop and I'm still getting null values for old_balance and new balance — jorame
– jorame, Commented Feb 1, 2014 at 1:03
@jorame - I suggest doing a bit more debugging, either in the IDE, or by using some PRINT statements to output the value of your variables at different stages of execution. Most likely the other problems are also related to variable-initialization. Float variables in T-SQL aren't auto-initialized to 0 as they might be in C#. — CJBS
– CJBS, Commented Feb 1, 2014 at 1:05

CJBS · Accepted Answer · 2014-02-01 00:54:11Z

1

@NEW_BALANCE hasn't been initialized. It's NULL, which is not greater than 0 (it's undefined).

DECLARE 
@NEW_BALANCE            FLOAT

Select @NEW_BALANCE

Output:

----------------------
NULL

(1 row(s) affected)

So, initialize it:

DECLARE 
@NEW_BALANCE            FLOAT = 0

Select @NEW_BALANCE

answered Feb 1, 2014 at 0:54

CJBS

15.8k7 gold badges98 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jorame Over a year ago

The problem is that it was inserting one record before I initialized it. I did as you have suggested and still the same issue. I get one record with NULL values in Old_Balance and New_Balance fields in the Payments table.

CJBS Over a year ago

Might be a problem with the overall code logic. Have a look at: msdn.microsoft.com/en-us/library/ms178642.aspx for some examples...

Collectives™ on Stack Overflow

While Loop in SQL not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related