07-29-03, 15:06 #1Registered User
- Join Date
- Jul 2003
Unanswered: is it possibl to avoid cursor (returned rows)
Normal set up
Select ( a bunch of crap..)
Usually I have to use the cursor to go through each record and do more crap with each record, for example I may email each user returned in the select. Is it possible to somehow use a stored procedure instead of a cursor or any other way to make this more efficient?
It's a batch job (run at night) and it'll return about 7,000 rows roughly each time so it's not terrible to the point where i need it to be super efficient, i'd just like to keep as little load on the sql server as much as possible. I assume it's one of those situations where I just can't avoid using the cursor.
07-29-03, 16:19 #2Registered User
- Join Date
- Feb 2002
- Houston, TX
If your select is simple, you can define a variable to hold the table key(s) and use a while loop to walk through the table.
If your select is complex you could dump the result set of the select into a table and then use a while loop/variable to walk the table.
declare @au_id varchar(11)
select @au_id = min(au_id) from pubs.dbo.authors
while @au_id is not null begin
select * from authors where au_id = @Au_id
select @au_id = min(au_id) from pubs.dbo.authors where au_id > @au_id
granted this is simple but it ilistrattes the pointPaul Young
(Knowledge is power! Get some!)