Data Management

Process multiple database records with SQL Server cursors

Under certain circumstances, your application may need to access a SQL Server database using cursors. This guideline explains the cursor syntax and outlines when the method is most applicable.

By Tony Patton

One of the better aspects of application development is that there are usually multiple ways to complete a task. For instance, when working with database systems, client-based code can be used to process the data or the processing can be moved to the database server via stored procedures.

SQL Server compiles stored procedures so that they are executed most efficiently. Of course, stored procedures may be used to handle the data selection of a subset of database records, but they may be used to process the individual records in the result set as well. This involves working with cursors.

Cursor syntax
A cursor is a means for accessing the individual rows in a table or result set. The rows are accessed one at a time. Accessing individual rows via cursors is like using a select statement to return a single row. SQL Server takes care of tracking the cursor position with the result set or table.

Before diving into the syntax, let's examine four cursor types:
  • FORWARD_ONLY—Rows are returned sequentially (you can only go forward) from data source, and changes made to the underlying data source appear instantly.
  • DYNAMIC—Similar to FORWARD_ONLY, but the sequential restriction is removed. You can access data using any order.
  • STATIC—Rows are returned as read only with changes to the underlying data source not shown. The data is dynamic, so it may be accessed in any order.
  • KEYSET—A dynamic data set with changes made to the underlying data appearing instantly, but insertions do not appear.

Let's take a closer look at cursor syntax:
DECLARE cursor_name CURSOR cursor_type
FOR select_statement
OPEN cursor_name
FETCH cursor_name
// work with record
CLOSE cursor_name
DEALLOCATE cursor_name

The DECLARE statement creates a variable of the cursor type. The cursor type is followed by the actual type (e.g., forward_only, dynamic, etc.). The FOR statement assigns the result set selection to the cursor. The OPEN command prepares the cursor for use; the cursor is opened so data may be retrieved from it. The FETCH command retrieves a single row from the cursor. The cursor is closed with the CLOSE command, and DEALLOCATE returns its resources to the system.

Cursor variables
There are two global variables that are important when working with cursors. These variables are:
  • @@CURSOR_ROWS—The number of rows in the cursor
  • @@FETCH_STATUS—Boolean value signaling success or failure of most recent fetch

These two variables are handy when accessing rows within a cursor result set. At this point, an example helps clarify cursor usage.

Cursors in action
The example in Listing A utilizes a cursor within a stored procedure. The procedure accepts a userID (i.e., key value for a user table), bookID to check out, and the requested start and end dates for check out. The procedure checks out the book, to the user, if it is available.

The code contains comments, but a few critical points are the use of the @@FETCH_STATUS global variable with the WHILE loop. This allows execution to continue until the contents of the cursor are exhausted. A fetch statement is called before the WHILE loop begins, and it is used again at the end of the WHILE loop.

This example does not address every aspect of working with cursors, but it does provide a peek at how they are used and coded. Refer to the SQL Server documentation for more information on developing your own.

This code could easily be produced using languages like VB with ADO or VB.NET/C# with ADO.NET. A record set could be returned from the database and processed using a similar approach.

Cursor usage
I tell developers to use cursors sparingly, and only when no alternate path can be found. The situation outlined in the example presents one scenario, but you may find a just alternative yourself. A couple of suggestions: Avoid a keyset and static cursors because temporary tables are created to store the underlying key values. Also, updating large amounts of data with a cursor may introduce problems with the time it takes to process the records, and locking problems may also result.

Editor's Picks