Nested Loops Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested Loops Question

Author

Topic

thefonz37
Starting Member

4 Posts

Posted - 2010-09-15 : 15:56:50

Why doesn't this statement behave like you might expect it to in another language? It only runs through the inner loop, but not the outer one. Can anyone help me fix it?

declare @flag1 integer
declare @flag2 integer
set @flag1 = 1
set @flag2 = 1
while @flag1 < 10 begin
while @flag2 < 10 begin
print cast(@flag1 as varchar)+' | '+cast(@flag2 as varchar)
set @flag2 = @flag2 + 1
end
set @flag1 = @flag1 + 1
end

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-15 : 16:25:29

I would say your code doesn't work on any platform


declare @flag1 integer; set @flag1 = 1
declare @flag2 integer; set @flag2 = 1

while @flag1 < 10 
  begin
	while @flag2 < 10 
	  begin
		print cast(@flag1 as varchar)+' | '+cast(@flag2 as varchar)
		set @flag2 = @flag2 + 1
	  end
	  Print @flag1
	set @flag1 = @flag1 + 1
  end

EDIT: Here's a hint..you need to reset flag2

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-16 : 05:00:04

Error is in red


declare @flag1 integer
declare @flag2 integer

set @flag1 = 1
set @flag2 = 1

while @flag1 < 10 begin

	while @flag2 < 10 begin
		print cast(@flag1 as varchar)+' | '+cast(@flag2 as varchar)
		set @flag2 = @flag2 + 1
	end
	set @flag1 = @flag1 + 1
	SET @flag2 = 1
end

Also -- it's a bad habit to CAST/CONVERT to VARCHAR without a dimension. You'll be caught out by that eventually. Best to always specify a width.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

thefonz37
Starting Member

4 Posts

Posted - 2010-09-16 : 10:55:47

Ahhh, I see - I'm still thinking in C++/Java mode where you don't have to manually reset everything.

The cast part was just to get some output from the loop to test to make sure it works - the actual loop will be doing something different.

Thanks guys.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-16 : 13:42:04

quote:
Originally posted by thefonz37

Ahhh, I see - I'm still thinking in C++/Java mode where you don't have to manually reset everything.

WAIT. Is that really True? I find that VERY hard to believe.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

thefonz37
Starting Member

4 Posts

Posted - 2010-09-17 : 09:37:23

Umm...yeah? Not sure why it's so hard to believe...

Well, in FOR loops that's how it works anyhow, but SQL doesn't seem to do FOR loops.

FOR( int i = 0; i < 10; i++ ) {
for( int j = 0; j < 10; j++ ) {
//do stuff
}
}

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-17 : 09:55:44

a FOR loop is NOT a WHILE Loop

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

thefonz37
Starting Member

4 Posts

Posted - 2010-09-17 : 14:19:15

Obviously they're different things, but it should be apparent that a FOR loop would have been a pretty natural fit in this situation. Amd as SQL doesn't have that capability, I had to approximate it using a WHILE so maybe you can begin to see where the semantics of the two things ran together.

Anyhow, this is really immaterial at this point anyhow, isn't it?

Feel free to close the thread - question asked, question answered.

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources