CODEWITHSUNDEEP
sqlsql-server-2008
jsp
jsp

Reputation: 2646

SQL While loop getting stuck in infinite loop

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

Upvotes: 0

Views: 4592

Answers (5)

Locky
Locky

Reputation: 132

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

Upvotes: 1

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

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

Upvotes: 0

rs.
rs.

Reputation: 27467

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

Upvotes: 0

John Puttman
John Puttman

Reputation: 40

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

Upvotes: 0

Gaston Flores
Gaston Flores

Reputation: 2467

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.

Upvotes: 0

Related Questions