SQL Server Cursor Functions

Cursor : A cursor allows looping through a record set and performing a certain operation on each record within the set. SQL Server supports three functions that can help you while working with cursors: @@FETCH_STATUS, @@CURSOR_ROWS and CURSOR_STATUS.

A cursor life cycle can be described as follows:

• Cursor is declared using the DECLARE CURSOR statement. This statement creates a cursor within SQL Server memory
• Cursor is activated using OPEN CURSOR statement. At this point you can populate the cursor with a record set.
• Data is retrieved from the cursor using the FETCH keyword.
• A WHILE loop is executed within the cursor to perform some operation with the rows in the cursor with the condition that the FETCH command is successful.
• Cursor is deactivated using CLOSE CURSOR statement. At this point you can't populate the cursor with additional rows. Nor can you work with rows within the cursor. However, you can re-open the cursor with OPEN CURSOR statement and perform additional work with the cursor.
• The cursor is destroyed using DEALLOCATE CURSOR statement. Once the cursor is de-allocated it cannot be reopened.

@@FETCH : The most commonly used cursor function is @@FETCH_STATUS. This function determines whether FETCH keyword has successfully retrieved a row from the current cursor.

@@FETCH_STATUS can take one of the three values:

@@FETCH_STATUS value           Meaning

0	Successful fetch of a row within a cursor
-1	Fetch has failed. This could mean that the cursor has reached the beginning (or end) of the record set. This could also mean that we attempted retrieving a record that does not exist. For instance, if you attempt to grab 51st record within a cursor that has 50 records fetch status will be 1.
-2	The fetched row is missing. This means the record you're trying to FETCH has been deleted or its key has been updated since you have opened the cursor. For example, the following cursor is populated with the top 5 customer names. While the cursor fetches rows successfully the @@FETCH_STATUS is 0. Once we get to the end of the result set @@FETCH_STATUS becomes -1: DECLARE @customer_full_name VARCHAR(85) DECLARE customer_cursor CURSOR FOR  SELECT TOP 5 FirstName + ' ' + MiddleName + ' ' + LastName FROM dimCustomer  OPEN customer_cursor  FETCH NEXT FROM customer_cursor INTO @customer_full_name WHILE @@FETCH_STATUS = 0  BEGIN   -- typically you'd do some row-based operation here  FETCH NEXT FROM customer_cursor INTO @customer_full_name  SELECT @@FETCH_STATUS AS fetch_status  END  CLOSE customer_cursor  DEALLOCATE customer_cursor Results: fetch_status  0 fetch_status  0 fetch_status  0 fetch_status  0 fetch_status  -1 @@CURSOR_ROWS: @@CURSOR_ROWS function returns the number of rows in the cursor which was opened last on the current connection. This means that if you have 3 cursors open @@CURSOR_ROWS will return the number of rows in the 3rd cursor. @@CURSOR_ROWS can take the following values: @@CURSOR_ROWS value          Meaning -m          Cursor is being populated asynchronously. "M" is the value of records in the record set. -1            The cursor is DYNAMIC; that means, it reflects the changes to the data within the cursor. Therefore the number of rows can change due to addition or deletion of rows in the underlying tables. DYNAMIC cursors always return 1 as value of @@CURSOR_ROWS. 0              This can mean one of the following:                Cursor has not been opened                Cursor has no rows                Cursor has been closed N             Number of rows in the cursor record set. N is reported after the cursor has been fully populated The following example shows you how @@CURSOR_ROWS value changes during the lifetime of the cursor: DECLARE  @last_name  VARCHAR(20),         @first_name VARCHAR(20) DECLARE MY_CURSOR CURSOR  FOR SELECT TOP 3 LASTNAME,             FIRSTNAME FROM   DIMCUSTOMER SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows' OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @last_name,     @first_name SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows' WHILE @@FETCH_STATUS = 0  BEGIN-- typically you'd do some row-based operation here     FETCH NEXT FROM MY_CURSOR    INTO @last_name,         @first_name  END CLOSE MY_CURSOR SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows' DEALLOCATE MY_CURSOR Results: cursor has 0 rows  cursor has 3 rows  cursor has 0 rows If the same cursor is executed with DYNAMIC keyword against the entire DimCustomer table within Adventure Works database the results would show "cursor has -1 rows" while the cursor was being searched. CURSOR_STATUS: The CURSOR_STATUS function can be used effectively within a stored procedure that calls another stored procedure, which returns an output parameter of CURSOR data type. This function can be used with local or global cursors and determines whether or not the stored procedure has returned a cursor with a result set. The syntax is: CURSOR_STATUS( 'local' or 'global', cursor name) or CURSOR_STATUS ('variable', cursor variable name) LOCAL or GLOBAL keywords allow you to specify the cursor scope; VARIABLE keyword specifies that CURSOR_STATUS function should examine a cursor variable. In order to use the CURSOR data type as an output parameter, you must specify VARYING keyword along with OUTPUT within the CREATE PROCEDURE statement.