Get started Bring yourself up to speed with our introductory content.

Transact SQL cursors

T-SQL cursors are used to repeat custom processing for each row of the cursor. The following excerpt outlines how to process a cursor for Transact-SQL Server and gives an example.

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.

    Transact-SQL cursors do not support processing blocks of records. Only one record can be fetched 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: 
    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
    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)
         Set @chvUnit = Coalesce(@chvUnit, '')
         If @debug <> 0
              Select @chvProperty Property,
                     @chvValue [Value],
                     @chvUnit [Unit]
         -- assemble list
         Set @chvProperties = @chvProperties + @chvProperty + '='
                            + @chvValue + ' ' +  @chvUnit + '; '
         If @debug <> 0
              Select @chvProperties chvProperties
         Fetch Next From @CrsrVar
         Into @chvProperty, @chvValue, @chvUnit
    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 = @intInventoryId

    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

    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:

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

    Basic Transact-SQL Programming Constructs

     Home: Introduction
     Tip 1: T-SQL identifiers
     Tip 2: Database object qualifiers
     Tip 3: Character string data types
     Tip 4: Date, time and number data types
     Tip 5: Special data types-Part 1
     Tip 6: Special data types-Part 2
     Tip 7: Local variables
     Tip 8: Global variables
     Tip 9: Table variables
     Tip 10: Flow control statements
     Tip 11: Blocks, looping and conditional statements
     Tip 12: unconditional and scheduled execution
     Tip 13: Transact SQL Cursors
     Tip 14: Cursor related statements
     Tip 15: Problems and justified use of cursors

    The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.

  • Dig Deeper on SQL-Transact SQL (T-SQL)