How to get the count of rows selected in a cursor

30 Jun 2005 8:34 AM

Cynthia

The @@cursor_rows should print 20 instead it is printing zero. Can anyone
help me in getting the total no of records fetched from the select statement
before opening the Claims cursor.

CREATE PROCEDURE Test AS

Declare @id varchar(10)
Declare Claims Cursor for
select top 20 [id] from <table>
print @@cursor_rows
Open Claims
Fetch Claims into @id
while @@Fetch_Status=0
Begin
print @id

Fetch Claims into @id
End
Close Claims
Deallocate claims

Thanx in Advance.

30 Jun 2005 9:15 AM

Jens Süßmeyer

@@cursor_rows is zero until you open it, so it should be:

> Open Claims
> print @@cursor_rows

HTH, Jens Suessmeyer.

Show quote

"Cynthia" <Cynt***@discussions.microsoft.com> schrieb im Newsbeitrag
news:37CA343C-F243-406A-9FEA-EC44B7411875@microsoft.com...
> The @@cursor_rows should print 20 instead it is printing zero. Can anyone
> help me in getting the total no of records fetched from the select
> statement
> before opening the Claims cursor.
>
>
> CREATE PROCEDURE Test AS
>
> Declare @id varchar(10)
> Declare Claims Cursor for
> select top 20 [id] from <table>
> print @@cursor_rows
> Open Claims
> Fetch Claims into @id
> while @@Fetch_Status=0
> Begin
> print @id
>
> Fetch Claims into @id
> End
> Close Claims
> Deallocate claims
>
>
>
> Thanx in Advance.

30 Jun 2005 9:21 AM

David Portas

@@CURSOR_ROWS applies only to an open cursor. Open the cursor before
you reference @@CURSOR_ROWS.

Why are you using a cursor here? Usually there are better alternatives
to cursors. Note that you've used TOP without ORDER BY, which gives an
undefined (=unreliable) result.

--
David Portas
SQL Server MVP
--

30 Jun 2005 10:11 AM

Cynthia

Thanks for the reply. Can you check this stored proc and tell me how to get
the no of rows in the cursor.

There are 8 rows in the publishers table. When I give the query select top
3 city from publishers the @@cursor_rows returns 3 instead if I give the
query select city from publishers it returns -1. How effectively can I use
this @@cursor_rows and is there any alternative?

CREATE PROCEDURE Test AS

Declare @id varchar(10)
Declare Claims Cursor for
select city from publishers
Open Claims
print @@cursor_rows
Fetch Claims into @id
while @@Fetch_Status=0
Begin
print @id

Fetch Claims into @id
End
Close Claims
Deallocate claims

Show quote

"Cynthia" wrote:

> The @@cursor_rows should print 20 instead it is printing zero. Can anyone
> help me in getting the total no of records fetched from the select statement
> before opening the Claims cursor.
>
>
> CREATE PROCEDURE Test AS
>
> Declare @id varchar(10)
> Declare Claims Cursor for
> select top 20 [id] from <table>
> print @@cursor_rows
> Open Claims
> Fetch Claims into @id
> while @@Fetch_Status=0
> Begin
>     print @id
>
>     Fetch Claims into @id
> End
> Close Claims
> Deallocate claims
>
>
>
> Thanx in Advance.

30 Jun 2005 10:20 AM

Tibor Karaszi

See the documentation in Books Online for @@CURSOR_ROWS. -1 means dynamic cursor, which is populated
dynamically. Obviously, TOP makes it a static or similar cursor. Below, for examples makes the
function return 8 rows.

Declare Claims INSENSITIVE Cursor for
select city from publishers

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Cynthia" <Cynt***@discussions.microsoft.com> wrote in message
news:9ADBF7EC-0FBE-476F-A8D0-EB51A35FE040@microsoft.com...
> Thanks for the reply. Can you check this stored proc and tell me how to get
> the no of rows in the cursor.
>
> There are 8 rows in the publishers table. When I give the query select top
> 3 city from publishers the @@cursor_rows returns 3 instead if I give the
> query select city from publishers it returns -1. How effectively can I use
> this @@cursor_rows and is there any alternative?
>
>
> CREATE PROCEDURE Test AS
>
> Declare @id varchar(10)
> Declare Claims Cursor for
> select city from publishers
> Open Claims
> print @@cursor_rows
> Fetch Claims into @id
> while @@Fetch_Status=0
> Begin
> print @id
>
> Fetch Claims into @id
> End
> Close Claims
> Deallocate claims
>
>
>
> "Cynthia" wrote:
>
>> The @@cursor_rows should print 20 instead it is printing zero. Can anyone
>> help me in getting the total no of records fetched from the select statement
>> before opening the Claims cursor.
>>
>>
>> CREATE PROCEDURE Test AS
>>
>> Declare @id varchar(10)
>> Declare Claims Cursor for
>> select top 20 [id] from <table>
>> print @@cursor_rows
>> Open Claims
>> Fetch Claims into @id
>> while @@Fetch_Status=0
>> Begin
>> print @id
>>
>> Fetch Claims into @id
>> End
>> Close Claims
>> Deallocate claims
>>
>>
>>
>> Thanx in Advance.

30 Jun 2005 10:26 AM

David Portas

>From Books Online:

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

You could try: DECLARE claims CURSOR STATIC FOR ...
but I recommend you first rethink just why you want to use a cursor for
this.

> How effectively can I use
> this @@cursor_rows and is there any alternative?

Yes, there are usually more effective alternatives. Since you haven't
fully explained what you are trying to achieve it's difficult to advise
you. If you just want to count the rows in a table:

SELECT COUNT(*) FROM YourTable

--
David Portas
SQL Server MVP
--

30 Jun 2005 10:56 AM

Cynthia

Thanks again. I just gave a scenario, this is not the actual procedure. But
we do have a requirement.

Show quote

"David Portas" wrote:

> >From Books Online:
>
> "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."
>
> You could try: DECLARE claims CURSOR STATIC FOR ...
> but I recommend you first rethink just why you want to use a cursor for
> this.
>
> > How effectively can I use
> > this @@cursor_rows and is there any alternative?
>
> Yes, there are usually more effective alternatives. Since you haven't
> fully explained what you are trying to achieve it's difficult to advise
> you. If you just want to count the rows in a table:
>
> SELECT COUNT(*) FROM YourTable
>
> --
> David Portas
> SQL Server MVP
> --
>
>