Cursors with SQL 2000 Part 2 - Page 2

WHERE CURRENT OF

With the cursor running as expected, it can be easily modified to update the values rather than display them. Modify the cursor to include the following update statements:

DECLARE @price money
DECLARE @get_price CURSOR

SET @get_price = CURSOR FOR
     SELECT price FROM titles

OPEN @get_price
 
FETCH NEXT FROM @get_price INTO @price

SELECT price FROM titles

WHILE (@@FETCH_STATUS = 0) 
BEGIN
 IF @Price < 20 
  UPDATE titles SET price = 
   (@price + (@price * .1)) 
  WHERE CURRENT OF @get_price
 ELSE
  UPDATE titles SET price = 
   (@price + (@price * .05)) 
  WHERE CURRENT OF @get_price

 FETCH NEXT FROM @get_price INTO @price
END

SELECT price FROM titles

CLOSE @get_price
DEALLOCATE @get_price

The selects show the expected results. Prices over twenty dollars have been raised by five percent while prices fewer than twenty dollars have been raised ten percent.

In the TSQL, the two new lines:

SELECT price FROM titles

were added only to show our changes. They are not needed for the update. The new key phrase "WHERE CURRENT OF" is now being used. The phrase tells SQL to work with the record at the current cursor position. Behind the scenes, SQL is using a dynamic cursor with optimistic locking on our cursor.

Cursor Types

There are three types of cursors, DYNAMIC, STATIC, and KEYSET. Dynamic cursors will show changes made on the base table as you scroll through the cursor. Static cursors copy the base table, "titles" in our case, to the tempdb. The cursor then reads from the tempdb, so any changes happening on the base table will not be reflected in the cursors scrolling. Keysets are in between Dynamic and Static cursors. A keyset will copy the base table's selected records keys into the tempdb, so the cursor will select the rows from the tempdb, but the data from the base table. So change to the base table will be seen, but new record inserts will not be.

Lock Types

There are also three types of locks for cursors, READ ONLY, SCROLL LOCKS, and OPTIMISTIC. A Read Only lock prevents any updates. It also cannot be used with the WHERE CURRENT OF statement. A scroll lock issues a hard lock on the underlining records. Any updates made by the cursor are guaranteed to succeed. Optimistic locking will look at the underlying record for any changes since the cursor fetched it, if the base tables record has changed, your update will fail. If not, the cursors update will succeed.

Optimization

The first rule of cursor optimization is not to use them if possible. In our above example, two update statements could have been used rather than a cursor. One statement would select books priced over twenty dollars, while the other selects books priced fewer than twenty dollars. This will usually out perform a cursor. If you are not going to be doing DML, use a read only or fast forward cursor rather than a dynamic scrollable cursor. Also, close and destroy the cursor when finished with it.

Conclusion

Sometimes the usual TSQL method of working with sets of records does not fit the application. If so, a cursor can be used to move SQL on a record-by-record basis. Cursors support a wide and useful array of commands to control everything from locks to visibility. Used in their correct format, cursors are reliable and invaluable.

» See All Articles by Columnist Don Schlichting