How to determine number of records in recordset returned by stored procedure?

[Bob Rafuse]

In TestStand 3.0 I am calling an SQL stored procedure where the stored
procedure returns a recordset. Everything appears to work (I can
iterate through the recordset and see that the data is valid).
However, I can not figure out how to easilly determine how many
records are actually in the recordset. Unlike the 'Open SQL
Statement' step, in the 'Data Operation' step that actually invokes
the stored procedure, there is no 'Number of Records Selected' option
to specify a TestStand variable to accept this value. I know I could
iterate through the returned recordset incrementing a counter until a
Fetch fails, but for larger recordsets, traversing the table multiple
times would be quite time consuming
. I am hoping to avoid this if
possible. Is there an easier way to get the number of records in a
recordset returned from a stored procedure call?

---
Bob

[Scott_Richardson]

Bob -
You are correct in that the new Set Parameters and Execution option in TestStand 3.0 available on the Database Data Operation step type does not export the number of records returned. The value is not available. If added, we would have to make that available in a future release. Unfortuneately you will have to either iterate through the recordset or move your database calls to an external code module.

Scott Richardson (NI)

[Bob Rafuse]

Scott,

Okay. Thanks for confirming that.

---
Bob

[Scott_Richardson]

Bob -
I altered the Database Data Operation step type code and tested a stored procedure that returned a recordset with SQL Server and the number of records in the statement was not returned with the recordset. So even if the feature was in the step type, the results would not be helpful.

Unfortuneately output parameters do not help either because they are not available until the statement is closed.

Scott Richardson (NI)

[Scott_Richardson]

Bob -
I also tried it with a query in Microsoft Access. Same result, no number or records.

Scott Richardson (NI)

[Scott_Richardson]

Bob -
The cursor type of the ADO Recordset object affects whether the number of records can be determined. The Recordset.RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

Because ADO does not let me set the cursor type for command objects which is what a stored procedure requires, it is up to the data source to determine the type of cursor and the support for record count.

Scott Richardson (NI)

[Bob Rafuse]

Scott,

I didn't realize the ADO command object Execute method always returned
a forward-only recordset and that there was no way to change this.
That explains the lack of a record count. The reason I asked the
original question was I could have ~sworn~ I'd used the RecordCount
property in VB for recordsets returned from stored procedures.
However, combing through all my old code now, I now see I only used
"while not EOF" type processing on such recordsets. My bad. Sorry
for the confusion.

Thanks for the info.

---
Bob

[anony-mouse]

So if the number of records doesn't work then how do you determine the number of records that was returned? Is there something else that will tell you?