Return Number of Rows affected by a Select statement (1 Viewer)

Sorry about this but I need your help.

I am looking for a way to return the number of rows from a select query to a variable in vbscript. I can get a value to return but it's not what I want. I get 1 if the SQL returned any Rows and 0 if it didn't.

I am using a RETURN VALUE parameter in my VB Script but I think the problem lies on the SQL side or more probably with me. If I fire a Stored Procedure containing a simple SELECT statement from SQL Query Tool, at the buttom of the Results I get a message like (14 row(s) affected). If I add RETURN @@Rowcount I get this message (1 row(s) affected). What I need (for paging purposes) is the value 14. I'd like to do it without firing two SQLs eg SELECT Count(*) and the the real SQL statement.

Anyone know how I can return the 14 and not the 1?

Thank You.

thought about opening up a recordset and doing a recordcount on the recordset? Such a small number of rows would not really effect speed in which the routine takes to run. Just remember to close the recordset once you have finished with it.

this might help..might not...take a look.
http://weblogs.asp.net/pwilson/archive/2003/10/10/31456.aspx

why not just run two selects in your stored procedure

one being the actual query the other being

select count(*)

using the same criteria

then output the value of select count(*) into an output parameter.






or


insert your select into a temporary table and use @@rowcount

Thanks everyone. Big2s post got me thinking (cheers BIG2). I know what I'm going to do now. I've tested it and works well and I'm happy with the response times etc. I followed Kodo's link and read long and hard and decided to do my paging server-side as it seems to be the most effiecient way. There seems to be a comprehensive study of paging here. Big thanks Kodo.

http://www.aspfaq.com/show.asp?id=2120

I am also following SQL_Hells advice but I'm only going to retrieve the record count when page = 1. By the way, I read somewhere that counting the Primary Key is quicker than count(*). Big thanks to you also SQLHell.

Take care

yes, select count(*) does have much overhead. I used the sproc in the link I gave and expanded on it to include more flexibility in the sproc for grouping etc. I was able to use this to build a gridview class for classic ASP that works quite well and the query is fast as it only selects the snapshot of records that are within that page and record count..