Example of a cursor code

Cursor
1. Prices for books below the current average price are up by 50%

2 prices are reduced by 25% for books above or equal to the current average price

The code is as follows:

DECLARE title_update cursor

For select Title_id,price from titles

For update

Go

Local variables

Declare @avg_price money, @title_id tid, @price

Open Title_update

BEGIN Tran

Calculate average book price

Select @avg_price =avg (price) from titles Holdlock

Fetch title_update into @title_id, @price

While @ @sqlstatus!=2

Begin

If @ @sqlstatus =1

Begin

Rollback Tran

RAISERROR 21001 "Fetch failed in cursor"

Close Title_update

DEALLOCATE cursor Title_update

Return

End

If @price < @avg_price

Price Increase 50%

Update titles Set price = Price * $1.50

Where CURRENT of Title_update

Else

Price Reduction 25%

Update titles Set price = Price * $.75

Where CURRENT of Title_update

If @ @error!=0

Begin

Rollback Tran

RAISERROR 22001 "Update failed"

Close Title_update

DEALLOCATE cursor Title_update

Return

End

Fetch title_update into @title_id, @price

End

Commit

Close Title_update

DEALLOCATE cursor Title_update

Go