SQL Server : Cursor Part 1

Cursors Definition

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

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  Customer CURSOR FOR SELECT Customer Name, ContractNumber, Service Duration, Amount, FROM Customer

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 Customer

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 Customer

 

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:

CLOSE Customer

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 Customer

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 Customer INTO @CustomerID, @ContractNumber, @Duration, @Amount

Continue Reading Cursor Part 2