SQL Server: Endless WHILE EXISTS loop

Question

I have problem with the following WHILE EXISTS loop. Could you consider what can be reason why it is endless loop and why it doesn't update values?

declare @part varchar(20)

while exists ((select top 1 * from part1 p where isnull(brojRacuna,'')=''))
begin
set @part=''
set @part=(select top 1 partija from part1 p where isnull(brojRacuna,'')='')
begin tran
update part1
set  BrojRacuna= (select dbo.dev_brojracuna (@part))
where partija like @part
print @part
commit
end

EDIT 1: Because I didn't find solution in first moment, I created cursor and updated data in that way. After that I found that left couple rows that are not updated, because function had an issue with data and couldn't update values for that rows. In that case, fields have been empty always and loop became endless.

I don't understand why you need "isnull(brojRacuna,'')=''" in exists statement. — bksi
– bksi, Commented Oct 3, 2011 at 8:01
I accidentally left one part of code: 'and partija='1111' ' We don't need it here, because I want to update all rows where BrojRacuna is NULL. — Nemanja Vujacic
– Nemanja Vujacic, Commented Oct 3, 2011 at 9:34

Jeremy · Accepted Answer · 2016-10-25 00:09:01Z

11

I don't understand why you select the partija value, since you have it in the where clause, you can simplify a lot this way:

declare @part varchar(20)

while exists ((select 1 from part1 p where isnull(brojRacuna,'')='' and partija='1111'))
begin
begin tran
update part1
set  BrojRacuna= (select dbo.dev_brojracuna ('1111'))
where partija like '1111'
commit
end

By the way, if you have an endless loop, maybe the function dev_brojracuna doesn't return the correct value, and brojRacuna remains unaltered.

edited Oct 25, 2016 at 0:09

Jeremy

5976 silver badges12 bronze badges

answered Oct 3, 2011 at 8:07

Dippi

3972 silver badges9 bronze badges

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

3 Comments

Nemanja Vujacic Over a year ago

As I said in Comment at my question: I accidentally left one part of code: 'and partija='1111' ' We don't need it here, because I want to update all rows where BrojRacuna is NULL.

Dippi Over a year ago

Ok, when I post there wasn't that comment :) Anyway, have you checked the brojRacuna function?

Nemanja Vujacic Over a year ago

Of course, I announced comment just in case that you haven't seen it. ;-) You can take a look at EDIT1, there is written about function. Thanks for answer and suggestions!

Collectives™ on Stack Overflow

SQL Server: Endless WHILE EXISTS loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related