Return Number of Rows affected by a Select statement

[david.brent]

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.

 

[Big2]

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.

 

[Kodo]

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

 

[SQL_Hell]

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

 

[david.brent]

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

 

[Kodo]

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