Intro

Most SQL statements generate rows of data called a result set (aka comsor, a COMplete Set Of Rows).

Usually a client wants this result set so he orders and gets a default result set (aka firehose "cursor") is used. The process for default result sets is like Joe calling Uno's for an order for 5-500 pizzas. The restaurant pumps the pizzas out as fast as they can and there is no additional communication between Joe and Uno's. Here is the basic process for a default record set:

  1. SQL Server gets a network packet from a client to execute some SQL.
  2. SQL Server executes the SQL.
  3. SQL Server immediately starts stuffing rows from the result set being generated into network packets and sends them to the client.
  4. The client receives the network packets and caches them in a network buffer. The client app uses the ODBC driver, OLE DB driver, or DB-Library DLL to get the rows from the buffer one at a time.

Default result sets are extremely efficient. Default result sets can be used with practically all SQL statements and can even execute batches or stored procedures that generate multiple result sets.

Sometimes though, you want to do something different: like access a fraction of the entire result set or use the result set by other code on the server. In that case you may want a cursor. A cursor (aka CURrent Set Of Records) is a mechanism for accessing a row or block of rows from a result set.

The main option for a cursor is whether the data is on the client or the server.

Once the client or server option is selected, the cursor is further specified by Cursor Type or Cursor Behavior, both of which basically do the same thing.

With these definitions in hand, it is possible to say that a fire hose cursor is, in one sense, a client-side cursor that is of Cursor Type Forward-Only and has a Cursor Behavior of non-scrolling and insensitive.

Implementing Cursors

The syntax for specifying a cursor is dependent on the method for making the cursor. SQL Server supports two possible methods for making cursors: via DECLARE CURSOR or via a DB API. Applications should choose one or the other but not both.

Please note that a cursor can be set to close automatically (or not) when a transaction is committed with this T-SQL syntax:

SET CURSOR_CLOSE_ON_COMMIT { ON | OFF }

There are two syntaxes to DECLARE a T-SQL variable of data type cursor, one is SQL-92 compliant, the other is a T-SQL version. The two cannot be mixed.:

DECLARE CursorName [INSENSITIVE] [SCROLL] CURSOR
FOR SelectStatement
[FOR {READ ONLY | UPDATE [OF ColumnName [, ...]]}]
DECLARE CursorName CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR SelectStatement
[FOR UPDATE [OF ColumnName [, ...]]]

There are two ways to use a variable of data type cursor in T-SQL.

Here is they syntax to FETCH from a T-SQL variable of data type cursor.

FETCH
[ [NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
  FROM
] 
{{[GLOBAL] CursorName} | @CursorVariableName} 
[INTO @VariableName [, ...]]

Here are how the different DB APIs fetch from cursors:

Examples

Example 1

Here is a basic T-SQL implementation of a firehose cursor.

USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
WHERE au_lname LIKE 'D%'
ORDER BY au_lname

OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM authors_cursor
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

Example 2

Here is a cursor nested inside of another to make a report. This example is straight out of the MSDN Library.

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
   @message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR 
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor 
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT " "
   SELECT @message = "----- Books by Author: " + 
      @au_fname + " " + @au_lname

   PRINT @message

   -- Declare an inner cursor based   
   -- on au_id from the outer cursor.

   DECLARE titles_cursor CURSOR FOR 
   SELECT t.title
   FROM titleauthor ta, titles t
   WHERE ta.title_id = t.title_id AND
   ta.au_id = @au_id   -- Variable value from the outer cursor

   OPEN titles_cursor
   FETCH NEXT FROM titles_cursor INTO @title

   IF @@FETCH_STATUS <> 0 
      PRINT "         <<No Books>>"     

   WHILE @@FETCH_STATUS = 0
   BEGIN
      
      SELECT @message = "         " + @title
      PRINT @message
      FETCH NEXT FROM titles_cursor INTO @title
   
   END

   CLOSE titles_cursor
   DEALLOCATE titles_cursor
   
   -- Get the next author.
   FETCH NEXT FROM authors_cursor 
   INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

-------- Utah Authors report --------
 
----- Books by Author: Anne Ringer
         The Gourmet Microwave
         Is Anger the Enemy?
 
----- Books by Author: Albert Ringer
         Is Anger the Enemy?
         Life Without Fear

Page Modified: (Hand noted: 2007-10-13 15:46:21Z) (Auto noted: 2007-11-17 06:43:43Z)