FETCH_STATUS function in sql server

@@FETCH_STATUS function determines whether FETCH keyword has successfully retrieved a row from the current cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.  

This function can have one of the three values:

Syntax of @@FETCH_STATUS Function :

@@FETCH_STATUS

Return type of @@FETCH_STATUS function is integer.

Examples of @@FETCH_STATUS Function :

Example 1 : Use of @@FETCH_STATUS function

DECLARE Customer_Cursor CURSOR FOR
SELECT ContactName FROM Customers
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor 
WHILE @@FETCH_STATUS = 0 
BEGIN
 FETCH NEXT FROM Customer_Cursor
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor

Above cursor displays each customer name one by one.

FETCH_STATUS function in sql server

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