I am listing a sample of cursor used in store procedure. You can refer to it. Either you can copy/paste and edit it or make you own from this example. Cursors are very useful in stored procedures. Cursor allows to get result just by passing a single query instead of multiple . I had listed an example of a hotel database.
create procedure au_sales (@author_id id)
as
Code:
/* 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 model number of
of mobile */
declare model_sales cursor for
select ta.title_id, t.title, t.total_sales
from titlemodel ta, titles t
where ta.title_id = t.title_id
and ta.au_id = @model_id
open model_sales
fetch model_sales
into @title_id, @title, @ytd_sales
if (@@sqlstatus = 2)
begin
print "The model is out of stock."
close model_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 +
" -- Record sales this month."
print @msg
end
else if (@ytd_sales < 875)
begin
select @msg = @title +
" -- Sale decreases this month."
print @msg
end
Bookmarks