Retrieving both a recordset and output parameters from a Stored Procedure

Within a block of programming to call a adCmdStoredProc through the
ADODB.Command object (appending parameters, etc), can the recordeset be
retrieved with

set rs = adoCmd.execute

as well as output parameters with

myoutparameter = .Parameters("@myoutparameter").Value

if said stored procedure contains a SELECT recordset as well as single value
SELECTs to my output parameter(s)?

What would this look like in .asp?

Thank You,

Julian

Re: Retrieving both a recordset and output parameters from a Stored Procedure

stjulian wrote:
> Within a block of programming to call a adCmdStoredProc through the
> ADODB.Command object (appending parameters, etc), can the recordeset
> be retrieved with
>
> set rs = adoCmd.execute
>
> as well as output parameters with
>
> myoutparameter = .Parameters("@myoutparameter").Value
>
> if said stored procedure contains a SELECT recordset as well as
> single value SELECTs to my output parameter(s)?
>
> What would this look like in .asp?
>

You need to realize: a stored procedure must return all resultsets BEFORE
returning any output or return parameters. This means you must completely
process all returned recordsets (usually by closing them) before attempting
to read the output or return parameters.

First thing: you need to prevent the stored procedure from passing the
informational "x rows were affected" messages by starting the body of the
procedure with "SET NOCOUNT ON". Since these are returned to the client as
resultsets, they will prevent the procedure from returning the output
parameters until they are processed (which nearly every newbie never
realizes he has to do).

Next, you must process the returned recordset, either by moving to its last
record, or by closing it. If the procedure returns multiple resultsets
(there is nothing preventing you from having multiple select statements in
the procedure), you must use NextRecordset to process all the returned
resultsets before attempting to read the output/return parameters.

Don't forget: there is a difference between output and return parameters.
See:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.general/msg/2c935bd7c531d82b

Given the above scenario: you have a procedure that uses an output parameter
and a select statement to return data, then you need to correctly build the
Command object's Parameters collection prior to executing it. You can use my
free code generator to ease this pain:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear

Then:
set rs = adoCmd.execute

Then, process the returned resultset. I suggest using GetRows to store the
data from the recordset into an array:
dim arData
if not rs.eof then arData=rs.GetRows
rs.close

Now read the output parameter:
myoutparameter = .Parameters("@myoutparameter").Value

Close and destroy the connection.

Process the data from the recordset:
if not isArray(arData) then
'no data was returned
else
'use the data from the array
end if

See this for more about using GetRows:
http://www.aspfaq.com/show.asp?id=2467


HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Retrieving both a recordset and output parameters from a Stored Procedure

I just forged ahead and tried it. Yup, it works. Amazing stuff these
computer things. Greatest thing since sliced bread. Really. Before sliced
bread I had to eat a whole loaf at a time. TWO if I wanted a sandwich ... My
carbs were through the roof ...

Julian


"stjulian" wrote in message
news:%23lnnukG8FHA.1864@TK2MSFTNGP12.phx.gbl...
> Within a block of programming to call a adCmdStoredProc through the
> ADODB.Command object (appending parameters, etc), can the recordeset be
> retrieved with
>
> set rs = adoCmd.execute
>
> as well as output parameters with
>
> myoutparameter = .Parameters("@myoutparameter").Value
>
> if said stored procedure contains a SELECT recordset as well as single
> value SELECTs to my output parameter(s)?
>
> What would this look like in .asp?
>
> Thank You,
>
> Julian
>

Re: Retrieving both a recordset and output parameters from a Stored Procedure

stjulian wrote:
> I just forged ahead and tried it. Yup, it works. Amazing stuff these
> computer things. Greatest thing since sliced bread. Really. Before
> sliced bread I had to eat a whole loaf at a time. TWO if I wanted a
> sandwich ... My carbs were through the roof ...
>

Well, hopefully you'll get some benefit from what i wrote ...
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Retrieving both a recordset and output parameters from a Stored Procedure

Absolutely did. Checked what I had done against your suggestions. We're on
the same level.

Thank you so much.

Julian


"Bob Barrows [MVP]" wrote in message
news:uM8Cp1G8FHA.4076@tk2msftngp13.phx.gbl...
> stjulian wrote:
>> I just forged ahead and tried it. Yup, it works. Amazing stuff these
>> computer things. Greatest thing since sliced bread. Really. Before
>> sliced bread I had to eat a whole loaf at a time. TWO if I wanted a
>> sandwich ... My carbs were through the roof ...
>>
>
> Well, hopefully you'll get some benefit from what i wrote ...
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>