Retrieving a recordset and Output parameters from a Stored Procedure

[stjulian]

If inside a stored procedure, there a SELECT statement to return a recordset
and another SELECT to set the value of an output parameter (as in SELECT
@OutValue = Name FROM table WHERE pkid=5), would 2 execute statements be
needed to return the OUTPUT parameter?

Like this?


adocmd.CommandTimeout = 120
adocmd.ActiveConnection = conn
adocmd.CommandType = adCmdStoredProc
adocmd.CommandText = "dbo.sprMyProcedure"

' write to database using spr
With adocmd

set param = .CreateParameter("@Value1", adInteger, adParamInput, 4, 15)
.parameters.append param
set param = .CreateParameter("@Value2", adInteger, adParamInput, 4, 19)
.parameters.append param

set param = .createparameter("@OutValue", adVarChar, adParamOutput, 50)
.parameters.append param

set rs = .execute
.execute
MyValue = .Parameters("@OutValue").Value






And, for that matter, how would I open the recordset so that it may be
restarted with

rs.MoveFirst

without getting

error '80040e18'
Rowset position cannot be restarted.

and, as well be able to retrieve the number of rows with rs.Recordcount ?

 

[Bob Barrows [MVP]]

If inside a stored procedure, there a SELECT statement to return a
recordset and another SELECT to set the value of an output parameter
(as in SELECT @OutValue = Name FROM table WHERE pkid=5), would 2
execute statements be needed to return the OUTPUT parameter?

Like this?


adocmd.CommandTimeout = 120
adocmd.ActiveConnection = conn
adocmd.CommandType = adCmdStoredProc
adocmd.CommandText = "dbo.sprMyProcedure"

' write to database using spr
With adocmd

set param = .CreateParameter("@Value1", adInteger, adParamInput,
4, 15) .parameters.append param
set param = .CreateParameter("@Value2", adInteger, adParamInput,
4, 19) .parameters.append param

set param = .createparameter("@OutValue", adVarChar,
adParamOutput, 50) .parameters.append param

set rs = .execute
.execute
MyValue = .Parameters("@OutValue").Value

Two things:
1. Your stored procedure should have a SET NOCOUNT ON statement in it to
supress the informational "x rows affected" messages that are returned as
resultsets.
2. Neither output nor return values are sent to the client until all
resultsets generated by thhe procedure are consumed by the client.

I find that using GetRows (in conjunction with NextRecordset if multiple
recordsets are returned) is an ideal way of dealing with this restriction

set rs = .execute
if not rs.eof then arData = rs.getrows
rs.close: set rs = nothing
MyValue = .Parameters("@OutValue").Value
conn.close: set conn = nothing

You now have a two-dimensional array containing your data - no need for the
wasteful MoveFirst.

You should go to msdn.microsoft.con/library and do a search for getrows to
see the documentation.
Also go to www.aspfaq.com and search for getrows to see a couple examples of
using a getrows array.