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.