CURSOR_ROWS function in Sql Server

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

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 
Tea

Above example returns output of executed cursor.

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

SELECT @@CURSOR_ROWS
CLOSE Product_Cursor
DEALLOCATE Product_Cursor

Output 
-1 Now 

@@CURSOR_ROWS function returns -1 i.e. cursor is dynamic.

CURSOR_ROWS function in Sql Server