SQL was designed as a set-oriented processing language. Some business rules( or poor physical design) require performing actions on row-by-row basis. Consider the following example:
. Increase the price of books <=$15 by 15%
. Decrease the price of books > $15 by 10%
Here is a set-oriented solution:
update titles set price = price *$1.15 where price < = $15
update titles set price = price *$0.9 where price > $15
Using Cursor:
Declare titles_curs cursor for
select ytd_sales, price from titles for update of price
go
declare @total_sales int, @price money
open titles_curs
fetch next from titles_curs into @total_sales,@price
if (@@fetch_status = -1)
begin
print ‘No books found’
close titles_curs
deallocate titles_curs
return
end
while (@@fetch_status = 0)
begin
if @total_sales = null
update titles set price = @price * 0.75 where current of titles_curs
else
if @price >$15
update titles set price = @price * 0.9 where current of titles_curs
else
update titles set price = @price * 1.15 where current of titles_curs
fetch next from titles_curs into @total_sales, @price
end
if (@@fetch_status = -2)
raiserror (‘Attempt to fetch deleted row’, 16,1)
close titles_curs
deallocate titles_curs
A major advantage of cursors is that you can avoid multiple passes of a table.
The advantage is performance; cursors introduce substantial overhead to a basic table scan.
Our recommendation : Use cursors to avoid many, many passes of the data. Seek alternative coding methods and compare for best performance, replace two update sentences to one:
update titles set price = price *
case
when price < 20 then 1.1
else 0.9
end