Using cursors in stored procedures

Cursors are particularly useful in stored procedures. They allow you to use only one query to accomplish a task that would otherwise require several queries. However, all cursor operations must execute within a single procedure. A stored procedure cannot open, fetch, or close a cursor that was not declared in the procedure. Cursors are undefined outside the scope of the stored procedure. See “Cursor scope”.

For example, the stored procedure au_sales checks the sales table to see if any books by a particular author have sold well. It uses a cursor to examine each row, and then prints the information. If you did not use a cursor, you would need several select statements to accomplish the same task. Outside stored procedures, you cannot include other statements with declare cursor in the same batch.

create procedure au_sales (@author_id id)
as
 
/* declare local variables used for fetch */
declare @title_id tid
declare @title varchar(80)
declare @ytd_sales int
declare @msg varchar(120)
 
/* declare the cursor to get each book written
    by given author */
declare author_sales cursor for
select ta.title_id, t.title, t.total_sales
from titleauthor ta, titles t
where ta.title_id = t.title_id
and ta.au_id = @author_id
 
open author_sales 
fetch author_sales
        into @title_id, @title, @ytd_sales 
if (@@sqlstatus = 2)
begin
    print "We do not sell books by this author."
    close author_sales
    return
end
 
/* if cursor result set is not empty, then process
    each row of information */
while (@@sqlstatus = 0)
begin
    if (@ytd_sales = NULL)
    begin
        select @msg = @title +
           " -- Had no sales this year."
        print @msg
    end
    else if (@ytd_sales < 500)
    begin
        select @msg = @title +
            " -- Had poor sales this year."
        print @msg
    end
    else if (@ytd_sales < 1000)
    begin
        select @msg = @title +
            " -- Had mediocre sales this year."
        print @msg
    end
    else
    begin
        select @msg = @title +
            " -- Had good sales this year."
        print @msg
    end
 
    fetch author_sales into @title_id, @title,
    @ytd_sales
end

For example:

au_sales "172-32-1176"

Prolonged Data Deprivation: Four Case Studies -- Had good sales this year.
 
(return status = 0)

For more information about stored procedures, see Chapter 17, “Using Stored Procedures.”