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.CommandT imeout = 120
adocmd.ActiveCo nnection = conn
adocmd.CommandT ype = adCmdStoredProc
adocmd.CommandT ext = "dbo.sprMyProce dure"

' write to database using spr
With adocmd

set param = .CreateParamete r("@Value1", adInteger, adParamInput, 4, 15)
.parameters.app end param
set param = .CreateParamete r("@Value2", adInteger, adParamInput, 4, 19)
.parameters.app end param

set param = .createparamete r("@OutValue" , adVarChar, adParamOutput, 50)
.parameters.app end param

set rs = .execute
.execute
MyValue = .Parameters("@O utValue").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 ?

--
Thank you,

Julian

[Bob Barrows [MVP]]

stjulian wrote:

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.CommandT imeout = 120
adocmd.ActiveCo nnection = conn
adocmd.CommandT ype = adCmdStoredProc
adocmd.CommandT ext = "dbo.sprMyProce dure"

' write to database using spr
With adocmd

set param = .CreateParamete r("@Value1", adInteger, adParamInput,
4, 15) .parameters.app end param
set param = .CreateParamete r("@Value2", adInteger, adParamInput,
4, 19) .parameters.app end param

set param = .createparamete r("@OutValue" , adVarChar,
adParamOutput, 50) .parameters.app end param

set rs = .execute
.execute
MyValue = .Parameters("@O utValue").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("@O utValue").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.
--
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.