@@CURSOR_ROWS function returns number of rows currently in the last opened cursor. The number returned by @@CURSOR_ROWS is negative if the last cursor was opened asynchronously. Keyset-driver or static cursors are opened asynchronously if the value for sp_configurecursor threshold is greater than 0, and the number of rows in the cursor result set is greater than the cursor threshold.
Below is table that describes cursor status based on return type.
Return value | Description |
-m | The cursor is populated asynchronously. The value returned
(-m) is the number of rows currently in the keyset. |
-1 | The cursor is dynamic. Because dynamic cursors reflect all
changes, the number of rows that qualify for the cursor is constantly
changing. It can never be definitely stated that all qualified rows have been
retrieved. |
0 | No cursors have been opened, no rows qualified for the
last opened cursor, or the last-opened cursor is closed or deallocated. |
n | The cursor is fully populated. The value returned (n)
is the total number of rows in the cursor. |
Syntax of @@CURSOR_ROWS Function :
@@CURSOR_ROWS
Return type of @@CURSOR_ROWS function is integer.
Examples of @@CURSOR_ROWS Function :
Example 1 : Use of @@CURSOR_ROWS function in select clause
SELECT @@CURSOR_ROWS
Output
0
Above example returns 0 means currently cursor is not opened.
Now we will execute @@CURSOR_ROWS after cursor is opened.
Below is the code to create, open and execute cursor.
DECLARE Product_Cursor CURSOR FOR
SELECT ProductName FROM Products
OPEN Product_Cursor
FETCH NEXT FROM Product_Cursor
Output
Above example returns output of executed cursor.
Below is the code to execute @@CURSOR_ROWS after cursor is executed. After that cursor is closed and deallocated.
SELECT @@CURSOR_ROWS
CLOSE Product_Cursor
DEALLOCATE Product_Cursor
Output
-1
Now @@CURSOR_ROWS function returns -1 i.e. cursor is dynamic.