Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Cursor FunctionsRSS Feeds

@@CURSOR_ROWS - CURSOR ROWS Function

Posted By: Sarita Patel     Category: SQL Server     Views: 10147

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_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.
  
Share: 

 
 

Didn't find what you were looking for? Find more on @@CURSOR_ROWS - CURSOR ROWS Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of @@CURSOR_ROWS - CURSOR ROWS Function is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!