counting the number of records with a stored procedure

[gavray]

I can count records returned when using SQL in a script, but how would I do that in a stored procedure without using count(*)?

Thanks

Gav

 

[vasah20]

Rs.Rowcount will return the number of records.
Make sure that your cursor is one that supports backwards movement, though, or else Rowcount will return -1.

For more info see Paul's FAQ:
faq333-618

hope this helps
leo

 

[dianal]

Hello
If you are using MS SQL Server
@@ROWCOUNT will do the task.
Hope this helps.
D.

 

[gavray]

thanks, for the suggestions. With @@rowcount in the stored procedure that was accepted but returned the error

rowset position cannot be restarted? how I call the count

ors.RowCount ?

cheers ears

 

[vasah20]

ok -
i'm a little confused. Where are you trying to get the number of records, in the stored proc or on the ASP page?

in the stored proc, dianal is right by saying that @@ROWCOUNT will work.

On the ASP page you can use my method and it should return what you need, again as long as your cursor supports movement.
ex:
'assuming you have imported ADOVBS.inc again
'check FAQ333-618 for cursor info
oRs1.CursorType = adOpenStatic

i hope this helps
leo

 

[gavray]

well I've got the sql queries in a stored procedure, when I try it Dinal's way I get the error does not support this method. I got it working fine before I used stored procedures, I just called or.RecordCount and

I used oRS52.CursorLocation=3,

I've tried oRS2.CursorType=adOpenStatic this returns nothing.

MY aim is to first count the number of records returned then display the first 10 threads etc. How would I call the value from @@RowCount on the asp page

thanks gav

 

[dianal]

Hello,
Well, may be @@ROWCOUNT is not suitable in your situation.
I'll try to post an example of using a stored procedure when have more time.

More likely is that you will need ADO.Recordset properties
AbsolutePage, PageCount and PageSize.
D.