Cursors

Relational databases are designed to work with sets of data. In fact, the purpose of the Select statement, as the most important statement in SQL, is to define a set of records. In contrast, end-user applications display information to the user record by record (or maybe in small batches). To close the gap between these conflicting requirements, RDBMS architects have invented a new class of programming constructs—cursors.

Many types of cursors are implemented in various environments using different syntax, but all cursors work in a similar fashion:

1. A cursor first has to be defined and its features have to be set.

2. The cursor must be populated.

3. The cursor has to be positioned (scrolled) to a record or block of records that needs to be retrieved (fetched).

4. Information from one or more current records is fetched, and then some modification can be performed or some action can be initiated based on the fetched information.

5. Optionally, steps 3 and 4 are repeated.

6. Finally, the cursor must be closed and resources released.

Cursors can be used on both server and client sides. SQL Server and the APIs for accessing database information (OLE DB, ODBC, DB-Library) all include sets of functions for processing cursors.

SQL Server supports three classes of cursors:

• Client cursors

• API server cursors

• Transact-SQL cursors

The major difference between Transact-SQL cursors and other types of cursors is their purpose. Transact-SQL cursors are used from stored procedures, batches, functions, or triggers to repeat custom processing for each row of the cursor. Other kinds of cursors are designed to access database information from the client application. We will review only Transact-SQL cursors.

Transact-SQL Cursors

Processing in Transact-SQL cursors has to be performed in the following steps:

1. Use the Declare Cursor statement to create the cursor based on the Select statement.

2. Use the Open statement to populate the cursor.

3. Use the Fetch statement to change the current record in the cursor and to store values into local variables.

4. Do something with the retrieved information.

5. If needed, repeat steps 3 and 4.

6. Use the Close statement to close the cursor. Most of the resources (memory, locks, and so on) will be released.

7. Use the Deallocate statement to deallocate the cursor.

Note 

Transact-SQL cursors do not support processing blocks of records. Only one record can be fetcbed at a time.

It is best to show this process through an example. We will rewrite the stored procedure that we used to illustrate the use of the While statement. The purpose of this stored procedure is to collect the properties of a specified asset and return them in delimited format (Property = Value Unit;). The final result should look like this:

      CPU=Pentium II;RAM=64 MB;HDD=6.4 GB;Resolution=1024x768;Weight=2 kg; 

Here is the code for the new instance of the stored procedure:

      Alter Procedure dbo.ap_InventoryProperties_Get_Cursor      /********************************************************************      Return comma-delimited list of properties that are describing asset.      Property = Value unit;Property = Value unit;Property = Value unit;...      Output:     @chvProperties      Return:     n/a                  Name            Date       Description      Created by: Dejan Sunderic 2005.04.18      Modified by:      test:      declare @p varchar(max)      exec dbo.ap_InventoryProperties_Get_Cursor 5, @p OUTPUT, 1      select @p      *****************************************************************/           (               @intInventoryId int,               @chvProperties varchar(max) OUTPUT,               @debug int = 0            )      As      declare  @intCountProperties int,               @intCounter int,               @chvProperty varchar(50),               @chvValue varchar(50),               @chvUnit varchar(50)      Set @chvProperties = ' '      Declare @CrsrVar Cursor      Set @CrsrVar = Cursor For          select Property, Value, Unit          from dbo.InventoryProperty InventoryProperty               inner join dbo.Property Property               on InventoryProperty.PropertyId = Property.PropertyId          where InventoryProperty.InventoryId = @intInventoryId      Open @CrsrVar      Fetch Next From @CrsrVar      Into @chvProperty, @chvValue, @chvUnit      While(@@FETCH_STATUS = 0)      Begin           Set @chvUnit = Coalesce(@chvUnit, '')           If @debug <> 0           Select @chvProperty Property,                  @chvValue [Value],                  @chvUnit [Unit]      -- assemble list      Set @chvProperties = @chvProperties + OchvProperty + '='                         + @chvValue + ' ' + @chvUnit + ' ; '      If @debug <> 0           Select @chvProperties chvProperties      Fetch Next From @CrsrVar      Into @chvProperty, @chvValue, @chvUnit End Close @CrsrVar Deallocate @CrsrVar Return 0 

The stored procedure will first declare a cursor:

      Declare @CrsrVar Cursor 

The cursor will then be associated with the collection of properties related to the specified asset:

      Set @CrsrVar = Cursor For          Select Property, Value, Unit          From dbo.InventoryProperty InventoryProperty               inner join dbo.Property Property               On InventoryProperty.PropertyId = Property.PropertyId          Where InventoryProperty.InventoryId = OintInventoryId 

Before it can be used, the cursor needs to be opened:

      Open @CrsrVar 

The content of the first record can then be fetched into local variables:

      Fetch Next From @CrsrVar      Into @chvProperty, @chvValue, @chvUnit 

If the fetch as successful, we can start a loop to process the complete recordset:

      While (@@FETCH_STATUS = 0} 

After the values from the first record are processed, we read the next record:

      Fetch Next From @CrsrVar      Into @chvProperty, @chvValue, @chvUnit 

Once all records have been read, the value of @@fetch_status is set to −1 and we exit the loop. We need to close and deallocate the cursor and finish the stored procedure:

      Close @CrsrVar      Deallocate @CrsrVar 

Now, let's save and execute this stored procedure:

      Declare @chvRes varchar(max)      Exec ap_InventoryProperties_Get_Cursor 5, @chvRes OUTPUT      Select @chvRes Properties 

SQL Server will return the following:

      Properties      --------------------------------------------------------------------      CPU=Pentium II ; RAM=64 MB; HDD=6.4 GB; Resolution=1024x768 ; Weight      =2 kg; Clock=366 MHz; 

Cursor-related Statements and Functions

Let's review statements and functions that you need to utilize to control cursors.

The Declare Cursor Statement

The Declare Cursor statement declares the Transact-SQL cursor and specifies its behavior and the query on which it is built. It is possible to use syntax based on the SQL-92 standard or native Transact-SQL syntax. I will display only the simplified syntax. If you need more details, refer to SQL Server Books OnLine.

      Declare cursor_name Cursor      For select_statement 

The name of the cursor is an identifier that complies with the rules set for local variables.

The Open Statement

The Open statement executes the Select statement specified in the Declare Cursor statement and populates the cursor:

      Open { { [Global] cursor_name }  cursor_variable_name} 

The Fetch Statement

The Fetch statement reads the row specified in the Transact-SQL cursor:

      Fetch  [  [ Next | Prior | First | Last                    | Absolute {n | @nvar}                    | Relative {n | @nvar}                 ]                 From             ]      { { [Global] cursor_name } | @cursor_variable_name}      [Into @variable_name [ , . . .n] ] 

This statement can force the cursor to position the current record at the Next, Prior, First, or Last record. It is also possible to specify the Absolute position of the record or a position Relative to the current record.

If the developer specifies a list of global variables in the Into clause, those variables will be filled with values from the specified record.

If the cursor has just been opened, you can use Fetch Next to read the first record.

@@ fetch status

@@fetch_status is a function (or global variable) that returns the success code of the last Fetch statement executed during the current connection. It is often used as an exit criterion in loops that fetch records from a cursor.

Success Code	Description
0	Fetch was completely successful.
-1	The Fetch statement tried to read a record outside the recordset (last record was already read) or the Fetch statement failed.
-2	Record is missing (for example, somebody else has deleted the record in the meantime).

@@cursor_rows

As soon as the cursor is opened, the @@cursor_rows function (or global variable) is set to the number of records in the cursor (you can use this variable to loop through the cursor also).

When the cursor is of a dynamic or keyset type, the @@cursor_rows function will be set to a negative number to indicate it is being asynchronously populated.

The Close Statement

The Close statement closes an open cursor, releases the current recordset, and releases locks on rows held by the cursor:

      Close { { [Global] cursor_name } | cursor_variable_name } 

This statement must be executed on an opened cursor. If the cursor has just been declared, SQL Server will report an error.

The Deallocate Statement

After the Close statement, the structure of the cursor is still in place. It is possible to open it again. If you do not plan to use it anymore, you should remove the structure as well, by using the Deallocate statement:

      Deallocate { { [Global] cursor name } | @cursor_variable_name} 

Problems with Cursors

Cursors are a valuable but dangerous tool. Their curse is precisely the problem they are designed to solve—the differences between the relational nature of database systems and the record-based nature of client applications.

First of all, cursors are procedural and thus contradict the basic idea behind the SQL language—that is, to define what is needed in a result, not how to get it.

Performance penalties are an even larger problem. Regular SQL statements are set-oriented and much faster. Some types of cursors lock records in the database and prevent other users from changing them. Other types of cursors create an additional copy of all records and then work with them. Both approaches have performance implications.

Client-side cursors and API server cursors are also not the most efficient way to transfer information between server and client. It is much faster to use a "fire hose" cursor, which is actually not a cursor at all. You can find more details about "fire hose" cursors in Hitchhiker's Guide to Visual Basic and SQL Server, 6th edition, by William Vaughn (Microsoft Press, 1998).

The Justified Uses of Cursors

The rule of thumb is to avoid the use of cursors whenever possible. However, in some cases, such avoidance is not possible.

Cursors can be used to perform operations that cannot be performed using set-oriented statements. It is acceptable to use cursors to perform processing based on statements, stored procedures, and extended stored procedures, which are designed to work with one item at a time. For example, the sp_addrolemember system stored procedure is designed to set an existing user account as a member of the SQL Server role. If you can list users that need to be assigned to a role, you can loop through them (using a cursor) and execute the system stored procedure for each of them.

Excessive processing based on a single row (for example, business logic implemented in the form of an extended stored procedure) can also be implemented using a cursor. If you implement such a loop in a stored procedure instead of in a client application, you can reduce network traffic considerably.

Another example could be the export of a group of tables from a database to text files using bcp. The bcp utility is a command-prompt program that can work with one table at a time. To use it within a stored procedure, you need to execute it using the xp_cmdshell extended stored procedure, which can run just one command at a time:

      Alter Procedure util.ap_Tables_BcpOut      --loop through tables and export them to text files      ©debug int = 0 As      Declare  ©chvTable varchar(128), ©chvCommand varchar(255)      Declare ©curTables Cursor      -- get all USER-DEFINED tables from current database Set OcurTables = Cursor FOR select name      from sysobjects      where xType = 'U'      Open OcurTables      -- get first table      Fetch Next From OcurTables      Into OchvTable      -- if we successfully read the current record      While (@@fetch status = 0)      Begin           -- assemble DOS command for exporting table           Set (SchvCommand = 'bcp "Asset5..[' + @chvTable                            + ']" out D:\backup\' + @chvTable                            + '.txt -c -q -Sdejan -Usa -Pdejan'           -- during test just display command           If @debug <> 0                Select @chvCommand chvCommand           -- in production execute DOS command and export table          If @debug = 0             Execute master.dbo.xp_cmdshell (SchvCommand, NO_OUTPUT          Fetch Next From @curTables          Into @chvTable      End      Close @curTables      Deallocate @curTables      Return 0 

If you execute this stored procedure (without specifying the ©debug parameter), SQL Server will execute the following sequence of command-prompt commands to export tables:

      bcp "Asset5.. [InventorySum]" out D:\backup\InventorySum.txt -c -q      -Sdejan -Usa -Pdejan      bcp "Asset5.. [EqType]" out D:\backup\EqType.txt -c -q      -Sdejan -Usa -Pdejan      bcp "Assets..[AcquisitionType]" out D:\backup\AcquisitionType.txt -c -q      -Sdejan -Usa -Pdejan      bcp "Asset5.. [Action]" out D:\backup\Action.txt -c -q -Sdejan -Usa -Pdejan      bcp "Asset5..[Contact]" out D:\backup\Contact.txt -c -q      -Sdejan -Usa -Pdejan      bcp "Asset5..[Contact_with_BC]" out_D:\backup\Contact_with_BC.txt -c -q      -Sdejan -Usa -Pdejan      bcp "Asset5..[EquipmentBC]" out_D:\backup\EquipmentBC.txt -c -q      -Sdejan -Usa -Pdejan      bcp "Asset5..[Inventory]" out D:\backup\Inventory.txt -c -q -Sdejan      -Usa -Pdejan      bcp "Asset5.. [InventoryProperty]" out D:\backup\InventoryProperty.txt      -c -q -Sdejan -Usa -Pdejan      bcp "Asset5.. [InventoryXML]" out D:\backup\InventoryXML.txt -c -q      -Sdejan -Usa -Pdejan 

Tip 

In Chapter 15, in the "A While Loop with Min() or Max() Functions" section, I will demonstrate another method for looping through a set of records using the While statement. Personally, I seldom use cursors; I prefer to use the method demonstrated in Chapter 15 for operations that cannot he implemented with set operations.