SQL stored procedures: display both output and recordset

I'd like to retrieve both a recordset and the output parameter from a
SQL stored procedure. I can do one or the other but not both. The
stored procedure itself is a simple query with an input and output
parameter.

The parameters:
oCmd.Parameters.Append oCmd.CreateParameter("@ID", adVarChar,
adParamInput, 15 , "9123456")
oCmd.Parameters.Append oCmd.CreateParameter("@Return_Code", adVarChar,
adParamOutput, 50, "")

If I use:
oCmd.Execute

I can read the output parameter using:
wscript.echo oCmd.Parameters("@Return_Code").Value
or
wscript.echo oCmd.Parameters(1).Value

If I use:
set rs = oCmd.Execute

I can loop through the recordset and correctly display the data. I
can't display the output parameter.

I tried to use:
Set rs = rs.NextRecordset
wscript.echo oCmd.Parameters("@Return_Code").Value
wscript.echo oCmd.Parameters(1).Value

That doesn't work.
I then tried to close the recordset:
Set rs = rs.NextRecordset
rs.close
wscript.echo oCmd.Parameters("@Return_Code").Value
wscript.echo oCmd.Parameters(1).Value
To no avail.

I did set nocount in the stored procedure. I can get either the
output data or the recordset data so the stored procedure is returning
the data to my script.

At this point all I want to return is "Hello world" from the stored
procedure.

I'm new to the world of running stored procedures from vbscript,
though I have been running queries for a long time. Any ideas?

Swyck

Bob
Fri Feb 17 13:50:41 CST 2006

Swyck@nospam.com wrote:
> I'd like to retrieve both a recordset and the output parameter from a
> SQL stored procedure. I can do one or the other but not both. The

SQL Server does not send the output or return values to the client until it
has finished sending all the resultsets generated in the procedure. For a
server-side forward-only cursor (the default) this means you at least have
to navigate to the last record in the recordset before you will be able to
read the output parameter value. I usually use GetRows to facilitate this:
set rs = oCmd.Execute
if not rs.eof then arData = rs.GetRows
rs.close: set rs = nothing
wscript.echo oCmd.Parameters(1).Value
'close and destroy the connection here
if isarray(arData) then
'process the array
end if


If you change the CursorLocation to 3 (adUseClient), then the entire
resultset is sent immediately, allowing you to read the output value right
away:

set rs=createobject("adodb.recordset")
rs.cursorlocation=3
rs.open ocmd
wscript.echo oCmd.Parameters(1).Value

Client-side recordsets are more expensive resource-wise than server-side
cursors, so my choice would be the getrows solution

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.