Reputation: 2646
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
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
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
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
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
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