Are Cursors in SQL Server Evil? Part Two: How to Use Cursors in Transact-SQL

Transact SQL ProgrammingIn the previous installment, we looked at the numerous options for declaring a cursor. All that remains is the easy part: fetching data from a cursor. To do this, we shall have to declare variables for each of the columns of the cursor. For purposes of clarity and ease of understanding, I see no reason not to use the column names as variable names.

DECLARE @CustomerID NCHAR(5), @OrderID INT, @OrderDate DATETIME, @ShipCountry VARCHAR(15)

Data is read into the variables using the FETCH statement. No tricks or surprises here:

FETCH Test_TSQL INTO @CustomerID , @OrderID , @OrderDate , @ShipCountry

After a row is fetched, the @@FETCH_STATUS global function can be used to determine whether the fetch succeeded.

/*

FETCH_STATUS

0 – success

-1 – end of result set

-2 – record has been deleted

*/

The Trap!

The potential problem is the third possibility, fetch status 2. Using an INSENSTIVE curosr (ANSI syntax) or a STATIC cursor (T-SQL syntax), there are only two possibilities. Either you succeed or you attempt to read past the end of the cursor. With cursors based on keysets, there is a third possibility. A record in an underlying table might have been deleted after the cursor keyset was generated. In this case, there is now a key pointing to a row that no longer exists. An attempt to access such a row will generate a tech status of 2. The problem occurs when developers only check for fetch success in their loops.

Many books, blogs, and magazine articles post example code that looks something like this:

WHILE @@FETCH_STATUS = 0 — only safe for INSENSITIVE cursors

BEGIN

PRINT @ShipCountry

FETCH Test_TSQL INTO @CustomerID , @OrderID , @OrderDate , @ShipCountry

END

If this loop were used to march through a keyset cursor of, say, 1000 rows, the possibility exists that the third row might have been deleted by some other user. In such a case, the loop would exit after processing only two of the one thousand rows, which would likely be a serious bug. Expanding the code a little bit, we create a loop that will simply continue with the next row if it encounters a deleted row.

WHILE @@FETCH_STATUS <> 1

BEGIN

IF @@FETCH_STATUS = 2

BEGIN

PRINT ‘Record deleted by another user’

FETCH Test_TSQL INTO @CustomerID , @OrderID , @OrderDate , @ShipCountry

CONTINUE

END

PRINT @ShipCountry

FETCH Test_TSQL INTO @CustomerID , @OrderID , @OrderDate , @ShipCountry

END

Options for FETCH

Implicit in the T-SQL examples shown here is that FETCH can be used as a shorthand for FETCH NEXT, in other words, fetch the next row.

FETCH NEXT FROM Test_TSQL INTO @CustomerID , @OrderID , @OrderDate , @ShipCountry

Note that if we explicity say “NEXT” we must also include the keyword FROM.

For most cursors FETCH NEXT is all you can do. It’s also likely all no you need to do. However, if you declare a cursor with the SCROLL option there are possibilities other than NEXT which can be explicitly invoked in a FETCH statement. These include: FIRST, LAST, and PRIOR. You can also access rows by absolute row number, or by number relative to the current position.

FETCH ABSOLUTE 7 FROM Test_TSQL INTO @CustomerID , @OrderID , @OrderDate , @ShipCountry

FETCH RELATIVE 2 FROM Test_TSQL INTO @CustomerID , @OrderID , @OrderDate , @ShipCountry

I suggest that the use of a fetch method other than NEXT be considered a red flag. Is the fetch method really necessary? Could your code logic be refactored to use an execution sequence based simply on NEXT?

Conclusion

Cursors suffer from a bad reputation that stemmed from poor cursor code and from the application of cursors to problems that were better solved with declarative SQL. Avoid these pitfalls and cursors will prove a valuable addition to your toolbox.

A zip file containing SQL code for this installment can be downloaded here.

For more, have a look at Learning Tree’s entire curriculum of 30 hands-on SQL Server courses. This includes 15 1-day sessions such as our new course on Writing Complex PL/SQL with Cursors and Collections.  All these courses can be taken online from home or office.

 

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.