By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Retrieving a recordset and Output parameters from a Stored Procedure

P: n/a
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 ?

--
Thank you,

Julian


Feb 8 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.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.
--
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.
Feb 8 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.