@@CURSOR_ROWS (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This function 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 of the @@CURSOR_ROWS
call.
Transact-SQL syntax conventions
Syntax
@@CURSOR_ROWS
Return types
int
Return value
Return value | Description |
---|---|
-m |
The cursor populates 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 constantly changes. The cursor doesn't necessarily retrieve all qualified rows. |
0 |
No cursors are open, 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. |
Remarks
@@CURSOR_ROWS
returns a negative number if the last cursor opened asynchronously. Keyset-driver or static cursors open asynchronously if the value for sp_configure
cursor threshold exceeds 0
, and the number of rows in the cursor result set exceeds the cursor threshold.
Examples
This example first declares a cursor, and then uses SELECT
to display the value of @@CURSOR_ROWS
. The setting has a value of 0
before the cursor opens and then has a value of -1
, to indicate that the cursor keyset populates asynchronously.
USE AdventureWorks2022;
GO
SELECT @@CURSOR_ROWS;
DECLARE Name_Cursor CURSOR
FOR
SELECT LastName, @@CURSOR_ROWS
FROM Person.Person;
OPEN Name_Cursor;
FETCH NEXT FROM Name_Cursor;
SELECT @@CURSOR_ROWS;
CLOSE Name_Cursor;
DEALLOCATE Name_Cursor;
GO
Here are the result sets.
-----------
0
LastName (No column name) ROWSTAT
--------- ----------------- ---------
Sánchez -1 NULL
-----------
-1