Retrieving a recordset and Output parameters from a Stored Procedure

S

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 ?
 
B

Bob Barrows [MVP]

stjulian said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top