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 of 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. Without the cursor, it 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 16, “Using Stored Procedures.” See also the Performance and Tuning Guide for information about how stored procedures that use cursors affect performance.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF