Need record count with recordset from stored procedure. 1

[grnzbra]

I have the following SP

CREATE PROCEDURE IALEIAMbrInfoSource
(
@WhereClause NVARCHAR(4000) = ''
)
AS
SET NOCOUNT ON
DECLARE @SQLstr NVARCHAR(4000)
SET @SQLstr = 'SELECT * FROM IALEIAMbrInfo' + @WhereClause + ' ORDER BY UniqID'
EXEC sp_executesql @SQLStr
GO

which I am calling from Access using

spstr = "IALEIAMbrInfoSource('" & strCriteria & "')"
Set rst = cnn.Execute(spstr)

In addition to the records, I also need a count of the records, but the rst.RecordCount of Access will only give me the number of records in the recordset if it has the proper cursor type. The SP is, apparently, not or the right cursor type.

Is there any way to set the SP up so it returns the records in a particular cursor type or any other way I can get a count along with the recordset?

 

[nigelrivett]

use a client side disconnected recorset - that will give you the record count.

Set the cursor type to client forward only and after the execute set the activeconnection on the recorset to nothing and close te connection.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[grnzbra]

Thank you for your suggestion. Howver, from your first signature line I get the impression that you believe that cursors should be avoided. If so, can you suggest a different approach?

 

[ArtieChoke]

I believe he's talking about cursors in SQL, not in Access via ADO. ADO forces cursors on you - there is no other option. It's just where you put the cursor that is under your control (client vs. server).

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[grnzbra]

Assuming I didn't have a need for a record count, which would be a more effective/efficient way of doing it, client side cursor or

set rst = cn.execute(StoredProcedure)

?

Which one ensures that the processing is done on the server side and only the selected records are sent across the network.?

 

[nigelrivett]

rst is a cursor - the question is whether it should be server or client side.
The processing will be done in the SP and that will be executed on the server whichever cursor location you use.

With a client side cursor the data is delivered when the recordset is created - no more network activity and memory freed on the server - and with a disconnected recordset you can be sure it's not holding any locks.
With the server side the data will be delivered when requested so less memory used on client and first rec should be faster but processing the entire cursor may be slower due to requesting data.

I try to make sure that clients have as little interaction with the server as possible so go for client side but if you have an (badly written?) app which processes large recordsets but then a server side cursor may be necessary.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.