When calling a stored proc, how can I get the number of records?

[tokuzumi]

I have a stored procedure that I need to know how many records were returned in the recordset. I have been reading up on the recordset.recordcount method, but everything seems to be for inline sql, not stored procedures. Any help is appreciated. Thanks.

 

[foxbox]

There is not that much difference between a recordset based on a SQL-stement or a Stored Procedure:

<%
set conn = server.CreateObject(&quot;ADODB.Connection&quot
conn.Open &quot;DSN=TT&quot;
set rs = server.CreateObject(&quot;ADODB.recordset&quot
rs.open &quot;MyStoredProc&quot;, conn

if not rs.eof
response.Write rs.recordcount
end if
%>

hth,
Foxbox

 

[tokuzumi]

Well, sorry to say, but that didn't help me. It looks like the recordcount method isn't very reliable. I tested your code on a stored proc that definately returns a recordset, and it gives me a value of -1. Also my stored procedure accepts two parameters....both integers.

 

[foxbox]

If the provider does not support this property or the count cannot be done, a value of -1 will be returned.

The type of cursor being used by the Recordset affects whether this property can return a valid count. In general, you can obtain the actual count for a keyset and static cursor. However, you may get either a -1 or the count if a dynamic cursor is being used, and you cannot get a count if a forward-only cursor is being used (-1 is returned).

For forward only recordsets recordcount always returns -1. Your alternative is to use rs.open with a different cursorset (adcursorStatic or adCursorDynamic
perhaps).

http://4guysfromrolla.com/webtech/112998-1.shtml

hth,
Foxbox