Check the Number of Rows Fetched

Use the @@rowcount global variable to monitor the number of rows of the cursor result set returned to the client up to the last fetch. This variable displays the total number of rows seen by the cursor at any one time.

In a nonscrollable cursor, once all the rows are read from a cursor result set, @@rowcount represents the total number of rows in that result set. The total number of rows represents the maximum value of @@cursor_rows in the last fetched cursor.

The following example determines the @@rowcount for the currently open authors_crsr cursor:

select @@rowcount
----------
        5
 
(1 row affected)

In a scrollable cursor, there is no maximum value for @@rowcount.The value continues to increment with each fetch operation, regardless of the direction of the fetch.

The following example shows the @@rowcount value for authors_scrollcrsr, a scrollable, insensitive cursor. Assume there are five rows in the result set. After the cursor is open, the initial value of @@rowcount is 0: all rows of the result set are fetched from the base table and saved to the worktable. All the rows in the following fetch example are accessed from the worktable.

fetch last authors_scrollcrsr   @@rowcount = 1
fetch first authors_scrollcrsr  @@rowcount = 2
fetch next authors_scrollcrsr   @@rowcount = 3
fetch relative 2 authors_scrollcrsr @@rowcount = 4
fetch absolute 3 authors_scrollcrs  @@rowcount = 5
fetch absolute -2 authors_scrollcrsr  @@rowcount = 6
fetch first authors_scrollcrsr  @@rowcount = 7
fetch absolute 0 authors_scrollcrsr  @@rowcount =7 (nodatareturned)
fetch absolute 2 authors_scrollcrsr  @@rowcount = 8