February 26, 2003 at 11:05 am
Hi all,
I am having problems in reading each record from a table and storing its contents in a variable. I am using the following code:
declare @id varchar(24)
declare @list varchar(8000)
declare @cnt int
declare @i int
select @cnt = count(distinct field1) from tableName
set @i = 1
while @i <= @cnt
begin
set rowcount @i
select top 1 @id = field1 from tableName
where field1 in (select field1 from tableName)
order by field1 desc
set @list = @id + ','
set @i = @i + 1
end
But the above query is returning only the last record. I guess I am messing up some where in the While loop. All the help will be greatly appreciated.
thanks in advance,
vijju
February 26, 2003 at 11:26 am
It seems you want something like this:
select @id = MAX( field1 ) from tableName
, @Row_Count = 1
while @Row_Count > 0
begin
select top 1 @id = field1 from tableName
where field1 < @id
order by field1 desc
set @Row_Count = @@ROWCOUNT
set @list = @id + ','
end
Although this kind of loops always look foreign to SQL language which supposed to be set-oriented, not one-row-at-a-time-oriented.
If yuour table should become relatively large, this loop will be taking forever for finish.
Michael
Edited by - mromm on 02/26/2003 11:27:49 AM
February 26, 2003 at 12:07 pm
thank you Michale, that helped to do what I wanted to do.
thanks,
Vijju
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply