This 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.
|-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.
LastName --------------- Sanchez