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