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