BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Cursors

The fifty-sixth part of the SQL Server Programming Fundamentals tutorial examines the use of cursors in Transact-SQL (T-SQL). Cursors are database objects that allow a data set that has been generated using a query to be processed a single row at a time.

Closing a Cursor

Once you have finished working with a cursor it must be closed to free the resources associated with it and to clear any locks that the cursor has created. To close the cursor, execute the following:

CLOSE BillingCursor

Deallocating a Cursor

A closed cursor can be reopened and reused multiple times. This is because the data structures of the cursor are not released when the cursor is closed. To release these structures and destroy the cursor after closing you should deallocate it. To deallocate the sample cursor, execute the following:

DEALLOCATE BillingCursor

Retrieving Cursor Data into Variables

The example above is interesting as it shows the use of a cursor. However, in a real-world situation it would not be particularly useful. Generally, you will wish to fetch the row data into variables that can then be used for further processing. For our example we require five variables for the five columns in the query. Declare the five variables as follows:

DECLARE @JobID UNIQUEIDENTIFIER
DECLARE @ContractNumber INT
DECLARE @Duration DECIMAL(4,2)
DECLARE @EngineerCost MONEY
DECLARE @PartsCost MONEY

To fetch the data from the cursor into the variables, use the INTO clause. This clause is followed by a comma-separated list of the variables to be populated. The first variable will receive the value of the first column in the query, the second from the second column and so on. One variable must be supplied for each of the query's columns.

FETCH NEXT FROM BillingCursor
INTO @JobID, @ContractNumber, @Duration, @EngineerCost, @PartsCost

Looping Through All Data in a Cursor

Cursors are commonly used with WHILE loops to process every row returned by the query individually. The first fetch operation is performed outside of the loop. This obtains the first row, if one is present, and sets a flag that specifies whether the cursor is exhausted or if further rows are present. The flag is accessed using the @@FETCH_STATUS function. If the fetch successfully found a row, the return value will be zero. A WHILE loop can use this function to determine whether further iterations are required.

To demonstrate, execute the following batch. This loops through all of the billing data. To simulate the integration to the third party system, information from each row is outputted to the Messages window.

DECLARE @JobID UNIQUEIDENTIFIER
DECLARE @ContractNumber INT
DECLARE @Duration DECIMAL(4,2)
DECLARE @EngineerCost MONEY
DECLARE @PartsCost MONEY

DECLARE BillingCursor CURSOR
FOR SELECT JobId, ContractNumber, Duration, EngineerCost, PartsCost
FROM BillingSystemOutputData

OPEN BillingCursor

FETCH NEXT FROM BillingCursor
INTO @JobID, @ContractNumber, @Duration, @EngineerCost, @PartsCost

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Sending job ' + CONVERT(VARCHAR(36), @JobID) + ' to billing system.'
    FETCH NEXT FROM BillingCursor
    INTO @JobID, @ContractNumber, @Duration, @EngineerCost, @PartsCost
END

CLOSE BillingCursor
DEALLOCATE BillingCursor

Insensitive Cursors

By default, cursors read each row of data from database tables as required, according to the query that is specified. If the data is modified after the cursor is opened, the modified data will be presented when fetched. If a user has deleted rows, these will not be accessible via the cursor. In some situations this is undesirable.

An insensitive cursor is not affected by changes to the underlying data. When the cursor is opened, a temporary table is created in the tempdb database. This table is populated with the results of the query immediately. Once populated, each fetch retrieves information from the temporary table, rather than the live data.

To specify that a cursor is insensitive, add the INSENSITIVE clause immediately after the cursor name in the declaration. For example:

DECLARE BillingCursor INSENSITIVE CURSOR
FOR SELECT JobId, ContractNumber, Duration, EngineerCost, PartsCost
FROM BillingSystemOutputData

Scrollable Cursors

A forward only cursor is also known as a non-scrollable cursor. The alternative is a scrollable cursor. Scrollable cursors are more flexible than forward only versions as they allow movement both forwards and backwards through the data. They also allow you to directly jump to the first or last rows or to a specific row number from the query. However, scrollable cursors generally give lower performance than forward only cursors.

To declare a scrollable cursor, add the SCROLL clause to the declaration as follows:

DECLARE BillingCursor SCROLL CURSOR
FOR SELECT JobId, ContractNumber, Duration, EngineerCost, PartsCost
FROM BillingSystemOutputData

When using a scrollable cursor, six FETCH commands are available:

  • FETCH NEXT. Retrieves the next row.
  • FETCH PRIOR. Retrieves the previous row.
  • FETCH FIRST. Retrieves the first row from the query results.
  • FETCH LAST. Retrieves the final row of the query results.
  • FETCH ABSOLUTE. Retrieves a specific row from the results. The row is provided as an integer. If positive, the row is counted from the start of the data set. A value of one indicates the first row, two indicates the second row and so on. If the value is negative, the row is counted from the end of the results. A value of -1 indicates that the last row should be retrieved.
  • FETCH RELATIVE. Used with an integer parameter n, this FETCH statement retrieves the row that is n results from the current row. A value of one retrieves the next row, -1 obtains the previous row, etc.
FETCH NEXT
FETCH PRIOR
FETCH FIRST
FETCH LAST
FETCH ABSOLUTE 3
FETCH RELATIVE -1

The following example fetches the last row from the cursor first. It then processes the rows in reverse order using FETCH PRIOR:

DECLARE @JobID UNIQUEIDENTIFIER
DECLARE @ContractNumber INT
DECLARE @Duration DECIMAL(4,2)
DECLARE @EngineerCost MONEY
DECLARE @PartsCost MONEY

DECLARE BillingCursor SCROLL CURSOR
FOR SELECT JobId, ContractNumber, Duration, EngineerCost, PartsCost
FROM BillingSystemOutputData

OPEN BillingCursor

FETCH LAST FROM BillingCursor
INTO @JobID, @ContractNumber, @Duration, @EngineerCost, @PartsCost

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Sending job ' + CONVERT(VARCHAR(36), @JobID) + ' to billing system.'
    FETCH PRIOR FROM BillingCursor
    INTO @JobID, @ContractNumber, @Duration, @EngineerCost, @PartsCost
END

CLOSE BillingCursor
DEALLOCATE BillingCursor
23 January 2010