Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Ohio/Chicago
    Posts
    75

    Unanswered: is it possibl to avoid cursor (returned rows)

    Normal set up

    cursor
    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.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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
    end

    granted this is simple but it ilistrattes the point
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •