What is wrong with this nested WHILE loop in SQL

Question

I ran into a weird situation today doing some one-time sql code. This nested loop doesn't seem to run the outer loop: it prints (0,0),(0,1),(0,2) and (0,3)

declare @i int, @j int
select @i = 0, @j = 0
while @i < 3 begin
    while @j < 3 begin
        select @i as i, @j as j
        set @j = @j + 1
    end
    set @i = @i + 1
end

Am I missing something blatantly obvious?

Thanks for this Q! I needed a big smile today. ;-)

Wim ten Brink
– Wim ten Brink

2010-01-07 13:55:23 +00:00
Commented Jan 7, 2010 at 13:55 — Wim ten Brink
– Wim ten Brink, Commented Jan 7, 2010 at 13:55

almog.ori · Accepted Answer · 2010-01-07 13:34:33Z

50

You are not resetting your j var for the next iteration

 set @i = @i + 1
 set @j = 0

answered Jan 7, 2010 at 13:34

almog.ori

7,8891 gold badge37 silver badges50 bronze badges

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

4 Comments

dkwarr87 Over a year ago

Seven years later and here I am having the same duh moment

ccalboni Over a year ago

@dkwarr87 it's been 11 years and I'm keeping the tradition alive

DannyD Over a year ago

I was going crazy until I found this. thanks!

Dylan Hayes Mar 6 at 22:42

15 years later, and here we are.

ozczecho · Accepted Answer · 2010-01-07 13:36:18Z

6

You are not resetting @j.

answered Jan 7, 2010 at 13:36

ozczecho

8,9098 gold badges39 silver badges42 bronze badges

Comments

Chjquest · Accepted Answer · 2012-04-20 13:15:46Z

2

declare @i int, @j int
select @i = 0, @j = 0 --<- Wrong place set @j
while @i < 3 
begin
    select @i, @j --<-test print, then you will know what happened~
    --set @j = 0 --<- Right place to set @j
    while @j < 3 
    begin
        select @i as i, @j as j
        set @j = @j + 1
    end
    set @i = @i + 1
end

The original result is 0/0 0/0 0/1 0/2 1/3 2/3

Well, the above answered, just add code for more detail, lol~

answered Apr 20, 2012 at 13:15

Chjquest

3353 silver badges10 bronze badges

Collectives™ on Stack Overflow

What is wrong with this nested WHILE loop in SQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related