Nemanja Vujacic Nemanja Vujacic - 1 month ago 8
SQL Question

SQL Server: Endless WHILE EXISTS loop

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.

Answer

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.

Comments