SQL Server Articles

@@CURSOR_ROWS - CURSOR ROWS Function

This article explains about @@CURSOR_ROWS function in sql server with examples.

@@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

ProductName

Chai

Above example returns output of executed cursor.

Below is the code to execute @@CURSOR_ROWSafter cursor is executed. After that cursor is closed and deallocated.