A Cursor is a database structure that allows for the traversal of the records in a result set. In Sybase T-SQL a cursor functions by the following steps:

1. Declare the Cursor:
   To use a cursor you first must declare it by providing a name and the SELECT query that will provide the result set it will be traversing through.  The Syntax for declaring the Cursor is as follows:

   DECLARE cursor_name CURSOR
   FOR  SELECT ....

2. Open the Cursor:
   Once the Cursor is declared we must open the cursor to utilize it:

   OPEN cursor_name

3. Fetch Rows:
   Now that the Cursor is ready we use the FETCH command to return a result row.  When fetching a row we need to fetch it into variables defined prior to the Fetch. To provide the user with if the query was successful the @@sqlstatus variable is available, where 0 means success, 1 = error, and 2 is no more data. We can use the @@sqlstatus variable in a WHILE condition to traverse the entire result set.

   FETCH cursor_name INTO @var1, @var2, ...
   WHILE ( @@sqlstatus = 0 )
   BEGIN
     // do something with the data
     FETCH cursor_name INTO @var1, @var2, ...
   END

4. Close the Cursor:
   After traversal of the result set we close the cursor by use of the CLOSE command:

   CLOSE cursor_name

5. Deallocate the Cursor:
   Last we deallocate the cursor so that Cursor is removed from memory and can not be opened again:

   DEALLOCATE CURSOR cursor_name

Optimization

Be advised that when using SELECT statements that JOIN multiple tables or have sub-queries that performance can be affected. To optimize it is good practice to perform such selects into a temporary table and that the cursor is set up to select data from that temp table.

Resources

• Sybase T-SQL Manual – Cursors