Looping through a recordset without a cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Looping through a recordset without a cursor

Author

Topic

Kanati
Starting Member

36 Posts

Posted - 2002-08-12 : 11:30:25

I want to return a recordset and conditionally do "foo" on each record in the dataset. IE:

SELECT * FROM FOO WHERE BLAH = 'Y' AND ACKNAME IS NOT NULL
{Import a file located in column ack}
{Process that file}
{UPDATE FOO SET BLAH = 'N' but only on the current record}
{next record}

I have seen something similar I think... without cursors... but I
can't remember where. Also, are extended stored procedures frowned
upon? I could write the process of importing, processing and updating the dataset in a powerbasic dll and save myself a major headache trying to learn advanced T-SQL on-the-fly so-to-speak (lotta hyphens there. :))

Thanks a lot.
Kanati

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-12 : 11:43:10

You must have an identifier to distinguish rows so something like
this - use any unique field
declare @filename varchar(100)

declare @id int,
@maxid int
select @id = 0, @maxid = max(id) from tbl where Process = 'Y'
while @id < @maxid
begin
select @id = min(id) from tbl where id > @id
select @filename = filename from tbl where id = @id

/*
import file here - dts, bcp, exe, ...
and check that it is successful
e.g. number of recs from control file
*/

update tbl set Process = 'N' where id = @id
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources