Re: SP - output parameter not coming back

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Axel wrote:
> Thanks a lot again,
>
> More valuable information, but also more complication. Basically I
> need to go through the whole recordset before I can access the output
> parameter; I might as well copy all the data and count myself. Or is
> there another alternative?
One of the links I provided earlier has the answer to this.
http://www.aspfaq.com/show.asp?id=2193


> (The reason I am still shying away from
> copying the rs into a local array is the typeless programming in ASP
> at least the data is nicely structured and neatly packaged while in
> the recordset)

I suspect you are putting the recordset to more use than it should be put in
ASP if you are worrying so much about datatypes. In ASP, recordsets should
only be used to retrieve read-only data for display/calculation purposes
only. A GetRows array lends itself quite nicely for this purpose.
http://www.aspfaq.com/show.asp?id=2467

>
> I still do not understand the NextRecordset bit, I am only asking for
> one resultset (my search results), are you saying all output
> parameters are in a separate recordset as well?

No. I am saying that IF the stored procedure returns multiple resultsets,
then all the resultsets must be sent to the clietn before output and return
parameter values are sent. Not using "SET OCOUNT ON" is a good way of
inadvertantly guaranteeing that multiple resultsets will be generated,
requiring the use of NextRecordset to process them all. NextRecordset ONLY
needs to be used if the procedure generates multiple resultsets.

> If this is the case

No, it is not the case, but ...

> is there any way to return this recordset FIRST?

No. I'm repeating myself, yet again. SQL Server waits until all resultsets
generated by the procedure are completely sent to the client before sending
output and return parameter values. There is no way to alter this behavior.

With a read-only server-side cursor, the entire resultset is NOT sent until
the last record in the resultset is loaded in the cursor. With a client-side
static cursor, the output and retrn parameters are available immediately
after opening the recordset. If you have a repro, perhaps using Northwind of
a situation where this is not the case, I would be interested in seeing it.

> All I want is the
> sum of records displayed before the table of results, which to the
> newbie (or the seasoned ADO programmer used to working in C++ / VB /
> Access environment) may sound pretty trivial.

Please re-read the article about recordset iteration I cited in my previous
reply (http://www.aspfaq.com/show.asp?id=2467). Using a GetRows array is a
very convenient way of achieving both of your goals. My usual procedure is
to open a recordset, immediately read its contents into an array using
GetRows, close the recordset, read te output and return parameter values
(which you would not need in this instance because you are only using the
output parameter to return the rowcount, something which can easily be
determined by using ubound on your GetRoes array), close and destroy te
connection and command objects, then process the array

> Also might it be
> possible to use the returnvalue instead, would that buy me anything?

No, it will not buy you anything. Again: SQL Server waits until all
resultsets generated by the procedure are completely sent to the client
before sending output and return parameter values. There is no way to alter
this behavior.

In general, while the return value can be used to return data, the general
convention is to use output parameters to return data, and return parameters
to return status/error codes.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: Record count
    ... Before opening the recordset, you need to set the cursortype to static, ... Of course, if you used GetRows to put your data into an array, you get the ... You can continue using the highly efficient default forward-only cursor ... You can optimize your connection usage by immediately closing your ...
    (microsoft.public.scripting.vbscript)
  • Re: Array or Recordset?
    ... What should be the deciding factor as to whether I create an array of ... current Recordset of data. ... changing the cursor, I wish to write new information to Row/Fieldname ...
    (microsoft.public.vb.database)
  • Re: Retrieving both a recordset and output parameters from a Stored Procedure
    ... a stored procedure must return all resultsets BEFORE ... Next, you must process the returned recordset, either by moving to its last ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • RE: Error 3021
    ... The only thing I see is that after you open the recordset, ... Create proto-file names using the selected job names and storre to an array ... Save and close the document and repeat the loop ...
    (microsoft.public.access.modulesdaovba)
  • Re: Passing Recordset to a Form
    ... Siteand Count which is the array length. ... to my recordset, but not into my database table. ... Dim rstview As adodb.Recordset ...
    (microsoft.public.vb.database)