Sybase T-SQL: How to use Cursors in your Stored Procedures

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 )
      // do something with the data
      FETCH cursor_name INTO @var1, @var2, ...
  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


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.


// T-SQL //

Comments & Questions

Add Your Comment