Strange WHILE loop problem - 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 2008 Forums

Transact-SQL (2008)

Strange WHILE loop problem

Author

Topic

kwikgal
Starting Member

4 Posts

Posted - 2011-02-15 : 07:00:24

I have a block of code as follows


	select @continue = 1
	while @continue > 0
	begin
		if not (expression)
			select @continue = 0
		
		--1st Check	
		if @continue = 1
		begin
			select @timeblock = max(endminutes) 
				from sitetariff 
				where [site]=@site 
					and endminutes > @minutesleft
					and startminutes <= @minutesleft 
			
			if @timeblock is null 
				select @continue = 0;
				 
			--2nd Check	
			if @continue = 1
			begin
				select	@dow = datepart(weekday, @starttime)
					
				--more code
			end
		end
	end

The problem is for some reason if @continue = 0 (2nd Check), the second time I check it, instead on getting out of the loop it seems to get stuck

Hope my explanation is clear.

Before using the above method, I tried using BREAK, but I was getting the same problem

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-15 : 07:34:06

quote:

--1st Check
Assume that for first time the value of @continue is 1

if @continue = 1
begin
--For the first time some value is returned by your select query let say 90 minutes

select @timeblock = max(endminutes)
from sitetariff
where [site]=@site
and endminutes > @minutesleft
and startminutes <= @minutesleft

--The below condition will not be satisfied and the value of @continue will be 1
if @timeblock is null
select @continue = 0;

Since the value of @continue is 1 it will enter the begin block
Second time also, I feel this is the reason because the value is still 1
--2nd Check
if @continue = 1
begin
select @dow = datepart(weekday, @starttime)

--more code
end
end

If you feel that my observation is wrong then please feel free to correct me.

kwikgal
Starting Member

4 Posts

Posted - 2011-02-15 : 08:21:06

thanks for your reply

I have tested @continue and it does become 0, in fact when @continue = 1, it does work.

Most cases the loop will end because in the 1st check the @continue = 0, the problem occurs when @continue = 0 before the 2nd check

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 09:52:40

can you provide the result of the following as per problematic parameter values?

select max(endminutes)
from sitetariff
where [site]=@site
and endminutes > @minutesleft
and startminutes <= @minutesleft

kwikgal
Starting Member

4 Posts

Posted - 2011-02-15 : 10:12:09

this comes out with a an empty record set for the ones I am having a problem with.

it should set @timeblock to null and that is what I am trying to test for and skip the rest of the processing as it needs to go to the next block of code

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-15 : 10:31:57

try adding the below line in your code:

set @timeblock = null

select @timeblock = max(endminutes)
from sitetariff
where [site]=@site
and endminutes > @minutesleft
and startminutes <= @minutesleft

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-15 : 10:34:04

The empty resultset doesnot overwrite the value of the variable:

For your understanding you can try this (I tried in sql2k)

declare @s datetime
set @s = getdate()

select @s = id from sysobjects where name ='nosuchobjectexists'
select @s

kwikgal
Starting Member

4 Posts

Posted - 2011-02-15 : 10:56:01

Thanks pk_bohra

The problem is not that the value @timeblock does not get set to null.

The problem is when @timeblock is null and therefore @continue = 0, it seems to get stuck instead of skipping


--2nd Check	
if @continue = 1
begin
select	@dow = datepart(weekday, @starttime)

--more code
end

and leaving the loop

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-02-15 : 11:56:09

There could be a better way than a loop for what you are trying to accomplish. We have no way to know without a lot more information.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources