469,913 Members | 2,234 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,913 developers. It's quick & easy.

Retrieving a recordset and Output parameters from a Stored Procedure

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
1 5140
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.

Similar topics

1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.