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.

What are Cursors?

A cursor is a database object that permits the data set generated by a query to be processed one row at a time. The cursor provides a pointer to a single row and allows the information in that row to be extracted and processed. Once that processing is complete, the cursor can be moved to another row. This allows row-by-row processing that may be difficult to accomplish using other techniques.

Commonly a cursor is combined with a WHILE loop to enable row-by-row processing of an entire data set. In such cases, the WHILE loop uses a cursor status function to determine whether the end of the data has been reached. If it has, the loop exits. If not, the next row is retrieved, or fetched, from the cursor and the loop restarts.

Considerations

Some developers and database administrators dislike database cursors. This is because cursors use resources whilst they are open and read data from the database each time a new row is fetched. This can increase network traffic, lower the available resources of the SQL Server instance and lead to poor performance. However, cursors can be useful when row-by-row processing is required, particularly within stored procedures that do not return the data and so do not generate excessive network traffic. As with all development techniques, you should consider all available options and select the most appropriate for the situation.

Using Cursors

The examples in this article use information from the JoBS tutorial database, which has been created and populated with data throughout the course of this series of articles. To obtain an up to date revision of the database, download and execute the script using the link at the top of this page.

NB: The examples do not show the best way in which to achieve the results. They are shown to demonstrate the syntax and usage of cursors only.

Billing Data View

One of the requirements of the JoBS database is the ability to export information to a third-party billing system. In this article I will demonstrate how this may be achieved using a cursor to output information one row at a time. To simplify the cursor examples, we will create a view that prepares the billing data. The cursor will query this view.

To create the billing system data view, execute the following script:

CREATE VIEW BillingSystemOutputData AS
SELECT
    J.JobId,
    J.ContractNumber,
    J.Duration,
    J.Duration * E.HourlyRate AS EngineerCost,
    (SELECT ISNULL(SUM(TotalCost),0) FROM UsedParts WHERE JobId = J.JobId)
        AS PartsCost
FROM
    Jobs J
INNER JOIN
    Engineers E
ON
    J.EngineerId = E.EngineerId

Declaring a SQL-92 Cursor

T-SQL provides two versions of the syntax for declaring a new cursor. In this article we will use the SQL-92 syntax. This uses a more standardised approach that is easy to migrate to other database management systems. We will not examine the more flexible, but less portable, extended syntax.

The basic syntax is as follows:

DECLARE cursor-name CURSOR FOR query

In this syntax, cursor-name is used to provide a name for the cursor. The query element is the SELECT statement that will be used to obtain information from the database. To declare a cursor that will use all of the data from the view created earlier, you can use the following:

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

Opening a Cursor

A cursor cannot be used until it has been opened using the OPEN statement. On opening, the query is executed and the cursor is populated. To open the billing cursor, use the following:

Once a cursor is opened you can determine the number of rows it has using the @@CURSOR_ROWS function. This function returns the number of rows of the latest cursor to be opened.

Fetching the Next Data Row

The cursor declared above is a forward-only cursor. This means that it is only possible to advance through the rows one by one. It is not possible to jump to a specific position, skip rows or to reverse through the data. As such, there is only one available command that can be used to obtain a row from the cursor. This command is "FETCH NEXT".

If you execute this command from SQL Server Management Studio you will see the first row of data displayed in the results area. Each subsequent execution of the fetch returns the next row until the cursor is exhausted.

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:

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:

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