Get Record Count from SQL Server Stored Procedure 1

[BobbaBuoy]

I am new to stored procedures and I am trying to get a record count from a recordset. Here is my stored procedure:

CREATE PROCEDURE sp_run_total 
(
	@beg_date datetime,
	@end_date datetime,
	@mycount bigint
 )
AS
         SELECT  @mycount = COUNT(*)
         FROM    RunTrng
         WHERE  (TrngDate BETWEEN @beg_date AND @end_date)
GO

And here is my asp:

'get number of run training sessions
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Exec sp_run_total '" & dBegDate & "', '" & dEndDate & "', '" & iRunTotal & "'", conn, adOpenStatic, adLockReadOnly
iRunTotal = rs.Recordcount
rs.Close
Set rs = Nothing

Any help would be much appreciated!

Bobba Buoy
Just trying to make sense of it all ...

 

[rac2]

Three ways to do that.
1. With the query you show, modify it slightly to have an alias for the COUNT(*) value. Then read it from the recordset as if it were a column name.

SELECT COUNT(*) AS "HowManyRecords"
         FROM    RunTrng
         WHERE  (TrngDate BETWEEN @beg_date AND @end_date)

and

iRunTotal = rs.Fields("HowManyRecords").value

2. Use an OUTPUT parameter
3. Specify the location of the query as the client and use rs.RecordCount.
I forget how to do this but it is property of the Recordset object.
rs.CursorLocation = adUseClient or something like that.

More later, its Sunday and the family is ready to leave for ice cream.

 

[BobbaBuoy]

Thanks. The first one worked. Are any of these methods faster than the others when working large tables?

Bobba Buoy
Just trying to make sense of it all ...

 

[rac2]

No, table size will affect all three the same. Table size affects the database engine not the ADODB objects. I dont think you would ever notice a performance difference on the web server side (the ASP processes).

You can always get both a record count and a recordset from one query using the RecordCount property of the Recordset, so that means you could get some rows of data and get the count in one operation.

Recordsets are the most common way to use ADODB objects, so they are familiar. OUTPUT parameters are less common and a bit more complicated to code. I use parameters with stored procedures which INSERT a record. I get the new identity value created by the INSERT from @@IDENTITY immediately following the INSERT, set that into an OUTPUT parameter and use that for further processing in the ASP script.