SQL Server Performance

How to use @@CURSOR_ROWS

Discussion in 'General Developer Questions' started by ks9072, Jun 16, 2005.

  1. ks9072 New Member



    Hi All,

    How to use @@CURSOR_ROWS ?? or Can i user this as like this:


    Open c1
    Set @i=1
    fetch c1 into val1,val2
    While i<>@@CURSOR_ROWS
    begin
    Set @i=i@+1
    ..
    ..
    ..
    fetch c1 into val1,val2
    end


    Regards
    Srini




  2. ranjitjain New Member

    FROM BOL:

    @@CURSOR_ROWS
    Returns the number of qualifying rows currently in the last cursor opened on the connection. To improve performance, Microsoft® SQL Server™ can populate large keyset and static cursors asynchronously. @@CURSOR_ROWS can be called to determine that the number of the rows that qualify for a cursor are retrieved at the time @@CURSOR_ROWS is called.

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

    Return Types
    integer

    Remarks
    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_configure cursor threshold is greater than 0, and the number of rows in the cursor result set is greater than the cursor threshold.

    Examples
    This example declares a cursor and uses SELECT to display the value of @@CURSOR_ROWS. The setting has a value of 0 before the cursor is opened, and a value of -1 to indicate that the cursor keyset is populated asynchronously.

    SELECT @@CURSOR_ROWS
    DECLARE authors_cursor CURSOR FOR
    SELECT au_lname FROM authors
    OPEN authors_cursor
    FETCH NEXT FROM authors_cursor
    SELECT @@CURSOR_ROWS
    CLOSE authors_cursor
    DEALLOCATE authors_cursor

    -----------
    0

    (1 row(s) affected)

    au_lname
    ----------------------------------------
    White

    (1 row(s) affected)

    -----------
    -1

    (1 row(s) affected)


  3. ks9072 New Member



    Hi,

    SELECT @@CURSOR_ROWS
    DECLARE authors_cursor CURSOR FOR
    SELECT au_lname FROM authors
    OPEN authors_cursor
    FETCH NEXT FROM authors_cursor
    SELECT @@CURSOR_ROWS
    CLOSE authors_cursor
    DEALLOCATE authors_cursor


    i have see the example in the online book ..SELECT @@CURSOR_ROWS is giving 0 even i got so many rows in the table


    Regards
    Srini
  4. sundeip New Member

    Use this statement , I have added "STATIC" to it.

    SELECT @@CURSOR_ROWS
    DECLARE authors_cursor CURSOR STATIC FOR
    SELECT au_lname FROM authors
    OPEN authors_cursor
    FETCH NEXT FROM authors_cursor

    SELECT 'cusrsor rows ' + cast(@@CURSOR_ROWS as varchar(20))
    CLOSE authors_cursor
    DEALLOCATE authors_cursor

    STATIC

    Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

    Remarks
    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_configure cursor threshold is greater than 0, and the number of rows in the cursor result set is greater than the cursor threshold.

    I hope this will clear your points

    Thanks
    Sandy
  5. benwilson New Member

    why not just use
    WHILE @@FETCH_STATUS = 0 ???
  6. ranjitjain New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by benwilson</i><br /><br />why not just use <br />WHILE @@FETCH_STATUS = 0 ???<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Hi ben @@fetch_status is useful when we process the recordset row by row till the fetch status is 0.<br />But srini needs the use of @@cursor_rows which keeps the no of rows currently in cursor opened.<br />So srini the values depends on the cursor you have opened if it is dynamic it will not populate the no of rows but if static then the recordsetset is fully populated and will return the no of qualifying rows which you can use as u want.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page