What is wrong with this SQL WHILE LOOP?

Question

This nested while loop is only successfully executing the lowest level loop. It refuses to add 1 to @wKey even though I tell it to SET @wKey = @wKey + 1.... What am I doing wrong here? Does SQL not allow nested loops?

DECLARE @fMinKey INT;
DECLARE @fMaxKey INT;
DECLARE @wMaxKey INT;
DECLARE @wKey INT;
DECLARE @wDate date;
DECLARE @fcStart DATE;

SET @fMinKey = (select min([fcKey]) from dbo.fact_Fc);

SET @fMaxKey = (select max([fcKey]) from dw.fact_Fc);

SET @wMaxKey = (select max([WellKey]) from dw.fact_Fc);

SET @wKey = 1;

SET @wDate = 
(select min([fapDate]) from dbo.dim_W where [Key] = @wKey);


SET @fcStart = 
(select min([Date]) from dw.fact_Fc where [wKey] = @wKey);


WHILE (@fMinKey <= @fMaxKey)
BEGIN

    WHILE (@wKey <= @wMaxKey)
    BEGIN
            WHILE (@wDate < @fcStart)
            BEGIN
                INSERT INTO dw.fact_FcTemp2 ([wKey], [Date], [pAmount], [fcKey], [AddedDate])
                VALUES (@wKey, @wDate, 0, @fMinKey, CURRENT_TIMESTAMP)
                SET @wDate = dateadd(DAY, 1, @wDate)
            END 
            SET @wKey = @wKey + 1

    END

    SET @fMinKey = @fMinKey + 1
END

The resulting table is only showing [wKey] = 1, but it should have rows for multiple different wKeys

because it's nested. The SET comes after the END of the first WHILE loop. In other words, after the first WHILE loop is completed, then I want to change the @wKey and restart the original loop all over again. — SUMguy
– SUMguy, Commented Oct 18, 2017 at 22:14

adopilot · Accepted Answer · 2017-10-18 22:15:02Z

1

Once when @wDate reach @fcStart looks like you never reset @wDate to initial state So next loop run again

You need some how to reset @wDate for next loop

Also having 3 loops perhaps is miss of design, sql performances does not like loops at all. Can you show us data structure and needed result maybe tehe is way to constuct sql whitout 3 nested loops

answered Oct 18, 2017 at 22:15

adopilot

4,58015 gold badges69 silver badges96 bronze badges

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

Collectives™ on Stack Overflow

What is wrong with this SQL WHILE LOOP?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related