Im trying to set up an application that uses a call to a stored procedure to

retrieves a recordset and outputs @@ROWCOUNT. based on the value of the

@@ROWCOUNT output it then decides wether the results should be paged or not.

The idea is based on Kens example of paging with a stored procedure and

.getrows.

The problem I'm running into is that I need the value of @@ROWCOUNT to

decide what to do with the recordset so I'm forced int a situation where I

have to run a objCommand.Execute to get @@ROWCOUNT and then objRS 

objCommand.Execute to get the recordset.

Is there another way that I could go about this? it seems pretty silly (not

to mention wastefull resources wise) to have to execute the the SP twice.



Thanks in advance

Jason

You can do all this with one stored procedure and return @@ROWCOUNT.



For example:



Create PROCEDURE test

        AS

	   select * from table

	        RETURN @@ROWCOUNT



then do the following:



set objComm = Server.CreateObject ("ADODB.Command") 'CREATE DB OBJ, RUN SP,

GET RESULTS

With objComm

.ActiveConnection = db

.CommandText = "test"

set rs = Server.CreateObject("ADODB.Recordset")

set p = .Parameters

p.Append .CreateParameter("@num_rows",adInteger,adParamReturnValue)

Set rs = .Execute

End With

  	rs.Close

     	intNoOfRecords = p(0).Value 

      rs.Open 



intNoOfRecords is your rowcount (passed back in the first parameter).  rs is

your recordset.  Be sure to close rs to get the return value, or it won't

work.  When you open it back up again, you still have access to the

recordset.  Good Luck.



Tom Garrison

Thanks for the help, but doesnt that still mean making two trips to the

server? Thats what I'm trying to avoid.



Jason

CREATE PROCEDURE MyProc

@Count int OUTPUT, @InputVar Int

AS



SELECT <fields>

FROM <table>

WHERE <conditions>



SELECT @Count = @@ROWCOUNT





(watch for wrapping)



<%

Set cmd = Server.CreateObject("ADODB.Command")

With cmd

	.ActiveConnection = cn

	.CommandType = adCmdStoredProc

	.CommandText = adCmdStoredProc

	.Paramaters.Append .CreateParameter("Count", adInteger,

adParamOuput)

	.Paramaters.Append .CreateParameter("InputVar", adInteger,

adParamInput, , MyVariable)

End With



Set rs = Server.CreateObject("ADODB.Recordset")

Set rs = cmd.Execute

intCount = cmd.Parameters("Count")



If Not rs.EOF Then arrReturn = rs.getRows



rs.Close

Set rs = Nothing

Set cmd = Nothing

%>



The code is untested and off the top of my head, so check it before use.

Drop your current Select statement into the proc.



regards

David Cameron

nOw.b2b

dcameron@....

You need to *close* the recordset before you can access the values of the

output parameters.



A wonderful feature of ADO & SQL Server when using server-side cursors.



Something that you might want to add (which isn't on my page) is to do a

dynamic SELECT TOP XX to reduce the size of the recordset being populated.

Then get the necessary records into the array, and close the recordset. Then

get the value of the output parameter.



Cheers

Ken

Since you are only executing the SP once, you only are really making one

trip to the server.  You are creating two ADO objects, but you only hit the

DB once with your Set rs = .Execute statement.  I'm not sure how you see

this as two trips.  As far as I can see, once the data is in the recordset,

you can close and reopen the recordset and you don't actually hit the db

again.



Tom Garrison







-----Original Message-----

From: Jason Byrnes [mailto:jasonbb@m...]

Sent: Thursday, October 18, 2001 6:04 PM

To: ASP Databases

Subject: [asp_databases] Re: recordset paging with stored procedure and

.g etrows





Thanks for the help, but doesnt that still mean making two trips to the

server? Thats what I'm trying to avoid.



Jason



"Garrison, Tom" <tom.garrison@e...> wrote in message

news:111963@a..._databases...

>

> You can do all this with one stored procedure and return @@ROWCOUNT.

>

> For example:

>

> Create PROCEDURE test

>         AS

>    select * from table

>         RETURN @@ROWCOUNT

>

> then do the following:

>

> set objComm = Server.CreateObject ("ADODB.Command") 'CREATE DB OBJ, RUN

SP,

> GET RESULTS

> With objComm

> .ActiveConnection = db

> .CommandText = "test"

> set rs = Server.CreateObject("ADODB.Recordset")

> set p = .Parameters

> p.Append .CreateParameter("@num_rows",adInteger,adParamReturnValue)

> Set rs = .Execute

> End With

>   rs.Close

>      intNoOfRecords = p(0).Value

>       rs.Open

>

> intNoOfRecords is your rowcount (passed back in the first parameter).  rs

is

> your recordset.  Be sure to close rs to get the return value, or it won't

> work.  When you open it back up again, you still have access to the

> recordset.  Good Luck.

>

> Tom Garrison

>

>

>

> -----Original Message-----

> From: Jason Byrnes [mailto:jasonbb@m...]

> Sent: Thursday, October 18, 2001 1:26 PM

> To: ASP Databases

> Subject: [asp_databases] recordset paging with stored procedure and

> .getrows

>

>

> Im trying to set up an application that uses a call to a stored procedure

to

> retrieves a recordset and outputs @@ROWCOUNT. based on the value of the

> @@ROWCOUNT output it then decides wether the results should be paged or

not.

> The idea is based on Kens example of paging with a stored procedure and

> .getrows.

> The problem I'm running into is that I need the value of @@ROWCOUNT to

> decide what to do with the recordset so I'm forced int a situation where I

> have to run a objCommand.Execute to get @@ROWCOUNT and then objRS 

> objCommand.Execute to get the recordset.

> Is there another way that I could go about this? it seems pretty silly

(not

> to mention wastefull resources wise) to have to execute the the SP twice.

>

> Thanks in advance

> Jason

>