| title | @@CURSOR_ROWS (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | @@CURSOR_ROWS (Transact-SQL) | ||||
| author | markingmyname | ||||
| ms.author | maghan | ||||
| ms.date | 08/18/2017 | ||||
| ms.prod | sql | ||||
| ms.technology | t-sql | ||||
| ms.topic | reference | ||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
This returns the number of qualifying rows currently in the last cursor opened on the connection. To improve performance, [!INCLUDEssNoVersion] 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
@@CURSOR_ROWS
[!INCLUDEsql-server-tsql-previous-offline-documentation]
integer
| 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 does not necessarily retrieve all qualified rows. |
| 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. |
@@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.
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 AdventureWorks2012;
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
---------------
Sanchez
-----------
-1