@@CURSOR_ROWS (Transact-SQL)


THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns the number of qualifying rows currently in the last cursor opened on the connection. To improve performance, SQL Server can populate large keyset and static cursors asynchronously. @@CURSOR_ROWS can be called to determine that the number of the rows that qualify for a cursor are retrieved at the time @@CURSOR_ROWS is called.

Topic link icon Transact-SQL Syntax Conventions



Return valueDescription
-mThe cursor is populated asynchronously. The value returned (-m) is the number of rows currently in the keyset.
-1The 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.
0No cursors have been opened, no rows qualified for the last opened cursor, or the last-opened cursor is closed or deallocated.
nThe cursor is fully populated. The value returned (n) is the total number of rows in the 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_configure cursor threshold is greater than 0 and the number of rows in the cursor result set is greater than the cursor threshold.

The following example declares a cursor and uses SELECT to display the value of @@CURSOR_ROWS. The setting has a value of 0 before the cursor is opened and a value of -1 to indicate that the cursor keyset is populated asynchronously.

USE AdventureWorks2012;  
SELECT LastName ,@@CURSOR_ROWS FROM Person.Person;  
OPEN Name_Cursor;  
FETCH NEXT FROM Name_Cursor;  
CLOSE Name_Cursor;  
DEALLOCATE Name_Cursor;  

Here are the result sets.








Cursor Functions (Transact-SQL)
OPEN (Transact-SQL)

Community Additions