Cursor @@rowcount returns 0 everytime

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

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

|||

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

|||

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

|||

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

|||

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.

|||

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

|||Thanks to all your comments. I've solve my problem.