The Cursor Solution

This solution will take more programming effort than the six-line CASE. Recall the five steps of cursor creation as you follow the code sample:

/* Declare the cursor */
declare price_curs cursor for
select price
from   titles
where  pub_id = ‘0736’
for update
go
/* Declare a local price variable to hold each row’s price */
declare @price money
/* Open the cursor */
open price_curs
/* Fetch first row */
fetch next from price_curs into @price
/* Looping construct. Loop until out of rows. */
while @@fetch_status = 0 begin
     if @price <= $10
         update titles
         set    price = price * 1.25
         where  current of price_curs
     else
         update titles
         set    price = price * 0.85
         where  current of price_curs
     fetch next from price_curs into @price
end
/* Cleanup */
close price_curs
deallocate price_curs

First, declare the cursor. Even though this cursor will be used to update rows, you must define a SELECT statement that retrieves the rows that may (or may not) be updated.

After declaring the cursor in its own batch, open the cursor. Seed @@fetch_status by fetching the first row in the keyset. Then, set up the loop. The loop runs until there is a problem or until there are no more rows to fetch.

Inside the loop, the price for the current row is tested. Two UPDATE statements are prepared. Notice the WHERE CURRENT OF clause. If you leave out this UPDATE modifier, the UPDATE runs against the whole table! This clause indicates that the current row in the cursor is the target of the UPDATE statement, not all rows in the base table.

Finally, once the loop is exited, close and deallocate the cursor.

---

Note:  Updating cursors
If the key value of the row is updated by the cursor, it will change the position of the row in the cursor keyset, creating the possibility that the row could be updated twice (or many times). Never update a key value inside a cursor. If you must, try to adjust key values to be earlier in the keyset, and use a forward-going cursor only.

---

Scrollable Cursors

Version 6.x introduced more flexible server cursors, which could maneuver inside a cursor keyset. So far, I have only shown how to move forward in a cursor. You may use other options with the FETCH statement to indicate which row should be fetched next. To use these other options, you must define your cursor as a scrollable cursor:

declare titles_curs scroll cursor
for
select title_id, price
from   titles
for read only

Scrollable cursors provide the following navigation operations. Examples of each follow:

NEXT fetches the next row. This is the default fetch direction.

PRIOR fetches the row immediately before the current row. PRIOR does not give you back the last row you fetched.

FIRST fetches the first row in the keyset.

LAST fetches the last row in the keyset.

ABSOLUTE n fetches row number n in the keyset.

RELATIVE n fetches the row number that is n rows away from the current row. You may use negative values for n. RELATIVE –1 fetches the prior row; RELATIVE 1 fetches the next row.

Scroll Cursor Examples

These examples demonstrate how to fetch with each of the scroll navigation keywords. If you try to navigate outside the keyset, you will receive no rows. This does not cause an error. If you are in a @@fetch_status loop, navigation outside the keyset returns –1.

NEXT

fetch next from titles_curs

This fetches the next row in the keyset. It is the default cursor operation.

PRIOR

fetch prior from titles_curs

Fetches the row preceding the current row. Note that this does not necessarily return the row that was last fetched. If you issue a fetch first, fetch last, and fetch prior, the current row would be the second-to-last in the keyset. Same as relative –1.

FIRST

fetch first from titles_curs

Fetches the first row in the keyset. FIRST is useful if you want to start at the top of the cursor keyset without closing and reopening the cursor.

LAST

fetch last from titles_curs

Fetches the last row in the keyset.

ABSOLUTE

fetch absolute 10 from titles_curs

Fetches the tenth row as measured from the beginning of the keyset.

RELATIVE

fetch relative 10 from titles_curs

Starting from the current row, fetches the tenth row. It skips over the nine rows between.

fetch relative @ctr from titles_curs

Move @ctr rows. You may use local variables or values from tables to determine which row to retrieve next.

fetch relative –3 from titles_curs

Move back three rows.

Cursor Syntax

This section details the syntax for each of the cursor operations.

This is the syntax for the declaration of a cursor:

DECLARE <cursor name> [INSENSITIVE] [SCROLL] CURSOR
FOR  <select statement>
[FOR {READ ONLY | UPDATE [OF <column-list>]}]

The INSENSITIVE option makes a copy of the data in tempdb. Insensitive cursors are read-only.

The SCROLL option allows the use of all FETCH operations. Nonscrollable cursors (forward-only cursors) may only use the FETCH NEXT operation.