Dominic Allkins writes "I'm currently using a ADO command to do a count of records for each category in a categories recordset - looping thru the recordset and running the count each time, i.e...Set cmd = Server.CreateObject.....While Not rs.EOF...cmd.CommandText = "SELECT Count(*) AS Count... etc.... WHERE Category = " & rs("CatID")...Set rsCount = cmd.Executers.MoveNextWendSet cmd = NothingThis works fine, but I'm trying to take the load off the web server and use the database server as much as possible, even for doing simple stuff like this... so I tried doing it with a stored procedure. The SP is a dynamic SP that uses a @WhereClause and EXEC - so I thought the simple thing would be to redefine the @WhereClause as I loop thru the recordset and call the SP each time, i.e.Set cmd = Server....While Not rs.EOF...WhereClause = searchSQL & rs("CatID")...cmd.Parameters ("@WhereClause") = WhereClause...Set rsCount = cmd.Executers.MoveNextWendSet cmd = NothingWhen I do it this way, it throws an error - too many arguments specified for SP as soon as it hits the second record in the categories recordset.I adapted it and re-instantiated the cmd object and closed it within the rs loop and it worked fine, but then all I'm doing is loading up the web server by closing and re-instantiating the cmd object everytime, this losing the benefit of using the SP - even if it only a small benefit in this example.Any ideas???ThanksDominic"
Re-using dynamic stored procedures in rs loop 
Posted - 2002-11-20 : 08:01:36
