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.

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:

OPEN BillingCursor

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.

PRINT @@CURSOR_ROWS -- Outputs "4"

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".

FETCH NEXT FROM BillingCursor

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.

23 January 2010