locked
Cursor @@rowcount returns 0 everytime RRS feed

  • Question

  • I everyone. I've got a very strange problem in my cursor. It's very strange because, if i execute my query without the cursor it returns one record, but if i use it in a cursor the @@rowcount returns 0.

    Code Snippet

    DECLARE CCursor CURSOR FAST_FORWARD

    FOR

    Select id_base, id_rc

    From Terceiros_Tarifarios

    Where codigo=@terceiro

    And data_fim>cast(cast(left(@document_ini,4) as varchar)+'-'+Cast(right(@documento_ini,2) as varchar)+'-01' as Datetime)

    Open CCursor

    set @cnt_del = @@rowcount

     

    if @cnt_del=0

    begin

    print 'No records'

    end

     

    ...

     

    @documento_ini (int) and @terceiro (varchar(20)) are set before cursor declaration

     

    Code Snippet

    set @documento_ini = 200607

    set @terceiro = '4170'

     

    Please help my in this tip.

    Thanks

     

     

     

     

     

     

    Monday, June 4, 2007 10:05 AM

Answers

  • @@CURSOR_ROWS is only used on the following STATIC & KEYSET cursors

    Code Snippet

    Declare @cnt_del as int

    Declare CCursor CURSOR STATIC –- or KEYSET

    FOR

     Select id_base, id_rc

                From Terceiros_Tarifarios

                Where codigo=@terceiro

                And data_fim>cast(cast(left(@document_ini,4) as varchar)

    +'-'+Cast(right(@documento_ini,2) as varchar)+'-01' as Datetime)

     

    Open CCursor

     

    Set @cnt_del = @@CURSOR_ROWS

     

    If @cnt_del=0

    Begin

                print 'No records'

    End

     

    Close CCursor

    Deallocate CCursor

     

    Monday, June 4, 2007 10:50 AM
  • cursor_status is not a valid function to verify for the record count, again if you use the dynamic cursor you always get the result as 1. (even there is no row).

    Finally , if you use the dynamic cursor you can’t get the record count/status  properly. (That’s what other providers like ADODB also says)

    To achieve your desired output you have to use either STATIC or KEYSET cursor rather than DYNAMIC or FAST_FORWARD.

    Monday, June 4, 2007 3:31 PM

All replies

  • @@Rowcount is not used for cursor row count. You have to use the @@FETCH_STATUS variable. The following logic can help you to achive your requirement..

     

    Code Snippet

    Declare CCursor Cursor FAST_FORWARD

    For

    Select * from Sysobjects;

    Open CCursor

     

    Declare @flag as Bit

    Set @flag = 0 --Set the inital flag as OFF

    Fetch next from CCursor Into .....

     

    While @@FETCH_STATUS = 0

    Begin

       Set @flag = 1 -- Reset the Flag

      --do your regular action here..

       Fetch next from CCursor Into .....

    End

     

    If @flag = 0 --If no records on the Cursor the flag value is still ZERO

    Begin

                Print 'No records'

    End

     

    Close CCursor

    Deallocate CCursor

     

     

    Monday, June 4, 2007 10:23 AM
  • Ok, so i've changed @@rowcount for @@cursor_rows and the output is -1.

    But how can i know how many rows are returned by cursor ?

    I need to execute some code if cursor returns no records, and with @@FETCH_STATUS i can't confirm that.

     

    edti: ok, i know u post a sample that uses @@FETCH_STATUS to know that, but i need to know it before a while @@fetch_status = 0

    Monday, June 4, 2007 10:38 AM
  • @@CURSOR_ROWS is only used on the following STATIC & KEYSET cursors

    Code Snippet

    Declare @cnt_del as int

    Declare CCursor CURSOR STATIC –- or KEYSET

    FOR

     Select id_base, id_rc

                From Terceiros_Tarifarios

                Where codigo=@terceiro

                And data_fim>cast(cast(left(@document_ini,4) as varchar)

    +'-'+Cast(right(@documento_ini,2) as varchar)+'-01' as Datetime)

     

    Open CCursor

     

    Set @cnt_del = @@CURSOR_ROWS

     

    If @cnt_del=0

    Begin

                print 'No records'

    End

     

    Close CCursor

    Deallocate CCursor

     

    Monday, June 4, 2007 10:50 AM
  • Tiago Salgado,

     

    Check function CURSOR_STATUS in BOL.

     

    ...

     

    -- you are not explicitly declaring if your cursor is local or global, so

    -- it will depend on your settings

    open CCursor

     

    if cursor_status('global', 'CCursor') = 0 

        print 'no records'

     

    ...

     

     

    AMB

    Monday, June 4, 2007 3:19 PM
  • cursor_status is not a valid function to verify for the record count, again if you use the dynamic cursor you always get the result as 1. (even there is no row).

    Finally , if you use the dynamic cursor you can’t get the record count/status  properly. (That’s what other providers like ADODB also says)

    To achieve your desired output you have to use either STATIC or KEYSET cursor rather than DYNAMIC or FAST_FORWARD.

    Monday, June 4, 2007 3:31 PM
  •  

    Sorry, but for static and keyset cursors it is as valid as @@rowcount. My first sentence says "Check function cursor_status in BOL", so if he/she does, then will notice the "*" beside "cursor name" and "cursor variable" in the table describing "return types".

     

    Anyway, thanks for your comment.

     

    use northwind

    go

     

    declare c cursor local static

    for

    select *

    from dbo.orders

    where 1 = 2

     

    open c

     

    select cursor_status('local', 'c'), @@rowcount, @@cursor_rows

     

    close c

    deallocate c

    go

     

    AMB

    Monday, June 4, 2007 3:49 PM
  • Thanks to all your comments. I've solve my problem.
    Monday, June 4, 2007 4:34 PM