SQL While loop getting stuck in infinite loop

Question

I 'm trying to write a simple while loop.

declare @colname as varchar =''

while @colname is not null
begin
  Select @colname = col1
  FROM Table1
  WHERE col1 in ('test1','test2','test3')

  if(@colname is not null)
   begin
    exec sp('@colname')
   end

end

It seems that it is getting the value of the last row it finds and keeps looping. Any suggestions on how to fix this.

UPDATE: I 'm calling a stored procedure for each value that is returned by the select statement. Instead of while the logic was written using cursors. So in effect trying to convert cursor to while loop. Thanks

I think it will never be null. @colname will contain the last value of your SELECT at the end of it. — Fabien TheSolution
– Fabien TheSolution, Commented Jul 12, 2013 at 17:08
Loops in SQL are usually not necessary; are you sure you need to be writing a loop? And, as others have pointed out, your SELECT statement will always return a value (unless there are no values of 'test1', 'test2', or 'test3') — Stuart Ainsworth
– Stuart Ainsworth, Commented Jul 12, 2013 at 17:10
What are you trying to accomplish with this while loop? It might help us provide advice on what to correct with your logic. — Adam Porad
– Adam Porad, Commented Jul 12, 2013 at 17:12
Even more, the value will always be 't' in this case since you didn't specified any lenght with varchar — Fabien TheSolution
– Fabien TheSolution, Commented Jul 12, 2013 at 17:16
Could you explain why you need a loop there... If the where clause doesn't change, can't you do it with a single SQL query? — Mash
– Mash, Commented Jul 12, 2013 at 17:18

Locky · Accepted Answer · 2013-07-14 22:45:17Z

1

when SELECT statement returns no rows then assignment of variable (@colname=colname) is not executed - and value of @colname remains unchanged - non-null, value from previous iteration - loop will continue forever

you need set @colname to null just before select statement - or check @@rowcount right after select statement to check if rows really were found - and if not - exit the loop

answered Jul 14, 2013 at 22:45

Locky

1324 bronze badges

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

Comments

Gaston Flores · Accepted Answer · 2013-07-12 17:22:16Z

0

I do not undestand your script, but maybe this will be useful:

declare @colname as varchar =''

while NULLIF(@colname,'') is not null
begin
  Select @colname = col1
  FROM Table1
  WHERE col1 in ('test1','test2','test3')
end

Your problem is on the "While condition", because '' <> NULL. Maybe you can this too:

while isnull(@colname,'') <> ''

or

while coalesce(@colname,'') <> ''

Anyway I suppose that your query is a little more complex for use this WHILE it this way.

edited Jul 12, 2013 at 17:22

answered Jul 12, 2013 at 17:14

Gaston Flores

2,4773 gold badges25 silver badges42 bronze badges

Comments

John Puttman · Accepted Answer · 2013-07-12 17:33:10Z

0

My guess is that you are trying to execute a series of stored procedures. These procedures are stored in table1.col1. I would do something like the following:

DECLARE @ColName VARCHAR(MAX)
SET @ColName = ''

SELECT @ColName = col1
FROM Table1
WHERE Col1 > @ColName
ORDER BY Col1

While @Colname IS NOT NULL
BEGIN 
EXEC SP(@colname)

SELECT @ColName = col1
FROM Table1
WHERE col1 > @ColName
AND col1 in ('test1', 'test2', 'test3')
ORDER BY col1

END

answered Jul 12, 2013 at 17:33

John Puttman

405 bronze badges

1 Comment

rs. Over a year ago

i think you need top 1 on your first select or else it will be set to last value lets say test9, this will cause your while loop select to not return any rows

rs. · Accepted Answer · 2013-07-12 17:55:14Z

0

Try this

declare @t table (colname varchar(10))
insert into @t
select distinct col1
FROM Table1
WHERE col1 in ('test1','test2','test3')

declare @colname as varchar =''
declare @cnt int = 0;

--set count used in loop condition
select @cnt = count(*) from @t

while @cnt > 0
begin
  -- get one row from table variable
  Select top 1 @colname = colname
  FROM @t

  --execute stored procedure
  if(@colname is not null)
   begin
    exec sp('@colname')
   end

  --delete row from table variable so that you don't read it again
  delete from @t where colname = @colname

  select @cnt = count(*) from @t
end

edited Jul 12, 2013 at 17:55

answered Jul 12, 2013 at 17:29

rs.

27.6k13 gold badges71 silver badges93 bronze badges

2 Comments

Fabien TheSolution Over a year ago

Almost this... but colname stick with his last value and the loop still infinite

rs. Over a year ago

@FabienTheSolution, that's true, i updated my code to handle last value

Fabien TheSolution · Accepted Answer · 2013-07-12 17:55:20Z

0

But if you really need do to this kind of stuff, if you want get out of the loop, try this :

declare @colname as varchar =''

while @colname is not null
begin
Select @colname = col1
FROM Table1
WHERE col1 in ('test1','test2','test3')
Select @colname = null
end

EDIT :

@rs almost had it.

Try this :

declare @t table (colname varchar(10))
insert into @t
select distinct col1
FROM Table1
WHERE col1 in ('test1','test2','test3')

declare @colname as varchar(10) =''
while @colname is not null
begin
  -- get one row from table variable
  Select top 1 @colname = colname
  FROM @t

  --execute stored procedure
  exec sp('@colname')

  --delete row from table variable so that you don't read it again
  delete from @t where colname = @colname
  --set @colname to null if there is no more value to process
if ((select count(*) from @t) = 0)
begin
select @colname = null
end
end

edited Jul 12, 2013 at 17:55

answered Jul 12, 2013 at 17:23

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

3 Comments

Fabien TheSolution Over a year ago

@rs Not true. Select colname = null will set the colname value to null and this will stop the loop.

rs. Over a year ago

yes thats true, but it will not execute SP for all the values found but only one.

Fabien TheSolution Over a year ago

@rs I know...the SP was not there when I answered first...and I exactly commented the same thing when the OP edited his question

Collectives™ on Stack Overflow

SQL While loop getting stuck in infinite loop

5 Answers 5

Comments

Comments

1 Comment

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related