SQL Server : Cursor part 2

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 @ CustomerID UNIQUEIDENTIFIER DECLARE @ContractNumber INT DECLARE @Duration DECIMAL(4,2) DECLARE @ Amount MONEY   DECLARE Customer CURSOR FOR SELECT Customer Name, ContractNumber, Service Duration, Amount FROM Customer   OPEN Customer   FETCH NEXT FROM Customer INTO @CustomerID, @ContractNumber, @Duration, @Amount   WHILE @@FETCH_STATUS = 0 BEGIN     PRINT 'Sending job ' + CONVERT(VARCHAR(36), @JobID) + ' to billing system.'     FETCH NEXT FROM Customer     INTO @CustomerID, @ContractNumber, @Duration, @Amount END   CLOSE Customer DEALLOCATE Customer

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

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

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 @ CustomerID UNIQUEIDENTIFIER DECLARE @ContractNumber INT DECLARE @Duration DECIMAL(4,2) DECLARE @ Amount MONEY   DECLARE Customer SCROLL CURSOR FOR SELECT @CustomerID, @ContractNumber, @Duration, @Amount FROM Customer   OPEN Customer   FETCH LAST FROM Customer INTO @CustomerID, @ContractNumber, @Duration, @Amount   WHILE @@FETCH_STATUS = 0 BEGIN     PRINT 'Sending job ' + CONVERT(VARCHAR(36), @JobID) + ' to billing system.'     FETCH PRIOR FROM Customer     INTO @CustomerID, @ContractNumber, @Duration, @Amount END   CLOSE Customer DEALLOCATE