Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121

    Unanswered: @@RowCount problem

    Hi I'm opening a RecordSet using the following code :

    declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode
    FROM TCertResults TSample
    WHERE (TSample.ISmpShortCode ='24/12359')

    Open trcl

    What I want to be able to get a count of this recordset. Been thru the TSQL help and it's pointing me toward the @@RowCount command but I can't seem to get this working.

    Any help would be apprieciated

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    @@ROWCOUNT returns the number of rows affected by the last statement. Use the @@CURSOR_ROWS function to receive the number of qualifying rows in the last opened cursor.

    BTW - do you really need cursor? It heavily hits DB performance.

  3. #3
    Join Date
    Feb 2002
    Location
    Sweden
    Posts
    34
    I agree,

    Won´t SELECT COUNT(*) FROM TCertResults TSample
    WHERE TSample.ISmpShortCode ='24/12359' do the trick?
    - Jonte

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No cursrors....No cursors...


    There is precious little you can do without them...

    Describe what your trying to do....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    The cursor is a lot more complicated than posted but the basic gist is that the results of the cursor will be 'fetched' into variables which will then be used to update other tables, etc. Then the variables will be repopulated using the fetch next command. When updating the other tables with the results of the original cursor, one of the values that I need to write is total amount of records from the original cursor.
    So I need the recordcount as soon as the cursor is open. But as I'm sure you've gathered I haven't got a clue how to get this!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Makes no sense to me...(being sober sucks...)

    So you want to write the count of the entire result set, for every row in the cursor?

    That seems to be a derivation of a thing...

    Anyway...do this before you open the cursor and just use the local variable..
    Code:
    DECLARE @x int
    SELECT @x=COUNT(*)
      FROM TCertResults TSample 
      WHERE ISmpShortCode ='24/12359'
    Still, you could do all of this without a cursor.

    Good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Looks for me you can use temp tables instead of cursors...

    But if you are decided to stay with them - didn't the @@CURSOR_ROWS suits you? Why - any reason? error? sth other?

  8. #8
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax

    declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
    FROM dbo.TSample INNER JOIN
    dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
    WHERE (dbo.TSample.ISmpShortCode = '24/12359')

    Open tcrl

    PRINT @@CURSOR_ROWS

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You don't my count solution?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    Your solution works great thank you.
    I was just continuing the thread in the hope that someone could tell me what the correct syntax @@Cursor_Rows function was.

    Cheers

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Look in BOL (Books Online):


    @@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.
    Don't use Cursors....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Originally posted by SexualChocolate
    Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax

    declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
    FROM dbo.TSample INNER JOIN
    dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
    WHERE (dbo.TSample.ISmpShortCode = '24/12359')

    Open tcrl

    PRINT @@CURSOR_ROWS
    The Great Holy Online Book says:
    "If you receive '-1' as return from @@CURSOR_ROWS that it means:
    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. You can try with Brett Kaiser's count solution. So says The Great Holy Online Book"

    Again:
    <ghost_voice>Beware of cursors.... uuuuaahahahahahaaa.....</ghost_voice>

  13. #13
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    like I say I'm fairly new to all this. I'm using a cursor because I don't know of another was to open a recordset and step thru it. If there is an alternative then please point me at it

  14. #14
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Count - to determine no of records,
    temp tables or derived tables to help recordset operations.

  15. #15
    Join Date
    Feb 2002
    Location
    Sweden
    Posts
    34
    People that normally write code in client tools tend to use cursors when writing code in SQLServer..that´s perfectly understood since that´s the way it´s done in vb, c, c# etc.

    BUT SqlServer is about batch updates (most of the time); lots and lots and lots of rows being updated/inserted/deleted at once, a cursor just isn´t efficient enough..

    My role is:If you are forced to use cursors or other types of loops in more than 1% of your sql code you have problaly done something wrong..
    - Jonte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •