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

Stored Procedure Output Parmeters

P: n/a
ASP Classic question:

I have a Stored procedure in SQL, that works, when tested in SQL, with one
input & several output parameters, as follows:
CREATE PROCEDURE myProcedure
@MyID int
, @First varchar(80) OUTPUT
, @Second varchar(80) OUTPUT
, @Third varchar(80) OUTPUT
, @Amount as numeric(18,0) OUTPUT
etc.

In ASP, however, using the following, the procedure executes it's internal
update function properly & sends back a return code of 0, however, only the
first output parameter is returned (iOrderID is assigned beforehand). The
rest of the output parameters come back blank:

cmdUpdate.CommandText = "myProcedure"
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@RETURN_VALUE",
adInteger, adParamReturnValue,0)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@OrderID",
adInteger, adParamInput,, iOrderID)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@First",adVarChar,adPar amOutput,80, sFirst)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@Second",adVarChar,adPa ramOutput,80, sSecond)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Third",
adVarChar, adParamOutput, 80, sThird)
set param =
cmdUpdate.CreateParameter("@Amount",adNumeric,adPa ramOutput,0, iAmount)
param.precision=18
param.numericscale=0
cmdUpdate.parameters.append param
cmdUpdate.Execute ,,adexecutenorecords

I have also tried retrieving/assigning the values after execution, as
follows, but to no avail:

cmdUpdate.CommandText = "myProcedure"
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@RETURN_VALUE",
adInteger, adParamReturnValue,0)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@OrderID",
adInteger, adParamInput,, iOrderID)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@First",adVarChar,adPar amOutput,80)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@Second",adVarChar,adPa ramOutput,80)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Third",
adVarChar, adParamOutput, 80)
set param =
cmdUpdate.CreateParameter("@Amount",adNumeric,adPa ramOutput,0)
param.precision=18
param.numericscale=0
cmdUpdate.parameters.append param
cmdUpdate.Execute ,,adexecutenorecords

sFirst =cmdUpdate("@First")
sSecond=cmdUpdate("@Second")
sThird=cmdUpdate("@Third")
iAmount=cmdUpdate("@Amount")

Is there some kind of limitation in ASP that you can only retrieve one
output parameter from a stored procedure? If not, how do I rectify this?
Thanks in advance.

Bari
Nov 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
In the above samples, my input parameter is @OrderID, I changed it to @MyID
in the SQL for purpose of example, but not in real life--the sql & asp do
match. Also, when I say I can only retrieve one output parameter, I mean
@First comes back ok, but not @Second, @Third, or @Amount

"Bari Allen" <ba**@sos.REMOVETHISNOSPAM.state.ga.us> wrote in message
news:eU**************@TK2MSFTNGP09.phx.gbl...
ASP Classic question:

I have a Stored procedure in SQL, that works, when tested in SQL, with one
input & several output parameters, as follows:
CREATE PROCEDURE myProcedure
@MyID int
, @First varchar(80) OUTPUT
, @Second varchar(80) OUTPUT
, @Third varchar(80) OUTPUT
, @Amount as numeric(18,0) OUTPUT
etc.

In ASP, however, using the following, the procedure executes it's internal
update function properly & sends back a return code of 0, however, only the first output parameter is returned (iOrderID is assigned beforehand). The
rest of the output parameters come back blank:

cmdUpdate.CommandText = "myProcedure"
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@RETURN_VALUE",
adInteger, adParamReturnValue,0)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@OrderID",
adInteger, adParamInput,, iOrderID)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@First",adVarChar,adPar amOutput,80, sFirst)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@Second",adVarChar,adPa ramOutput,80, sSecond)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Third",
adVarChar, adParamOutput, 80, sThird)
set param =
cmdUpdate.CreateParameter("@Amount",adNumeric,adPa ramOutput,0, iAmount)
param.precision=18
param.numericscale=0
cmdUpdate.parameters.append param
cmdUpdate.Execute ,,adexecutenorecords

I have also tried retrieving/assigning the values after execution, as
follows, but to no avail:

cmdUpdate.CommandText = "myProcedure"
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@RETURN_VALUE",
adInteger, adParamReturnValue,0)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@OrderID",
adInteger, adParamInput,, iOrderID)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@First",adVarChar,adPar amOutput,80)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@Second",adVarChar,adPa ramOutput,80)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Third",
adVarChar, adParamOutput, 80)
set param =
cmdUpdate.CreateParameter("@Amount",adNumeric,adPa ramOutput,0)
param.precision=18
param.numericscale=0
cmdUpdate.parameters.append param
cmdUpdate.Execute ,,adexecutenorecords

sFirst =cmdUpdate("@First")
sSecond=cmdUpdate("@Second")
sThird=cmdUpdate("@Third")
iAmount=cmdUpdate("@Amount")

Is there some kind of limitation in ASP that you can only retrieve one
output parameter from a stored procedure? If not, how do I rectify this?
Thanks in advance.

Bari

Nov 18 '05 #2

P: n/a
Ask this question on the ASP Classic newsgroup:
microsoft.public.inetserver.asp.general.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Bari Allen" <ba**@sos.REMOVETHISNOSPAM.state.ga.us> wrote in message
news:eU**************@TK2MSFTNGP09.phx.gbl...
ASP Classic question:

I have a Stored procedure in SQL, that works, when tested in SQL, with one
input & several output parameters, as follows:
CREATE PROCEDURE myProcedure
@MyID int
, @First varchar(80) OUTPUT
, @Second varchar(80) OUTPUT
, @Third varchar(80) OUTPUT
, @Amount as numeric(18,0) OUTPUT
etc.

In ASP, however, using the following, the procedure executes it's internal
update function properly & sends back a return code of 0, however, only the first output parameter is returned (iOrderID is assigned beforehand). The
rest of the output parameters come back blank:

cmdUpdate.CommandText = "myProcedure"
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@RETURN_VALUE",
adInteger, adParamReturnValue,0)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@OrderID",
adInteger, adParamInput,, iOrderID)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@First",adVarChar,adPar amOutput,80, sFirst)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@Second",adVarChar,adPa ramOutput,80, sSecond)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Third",
adVarChar, adParamOutput, 80, sThird)
set param =
cmdUpdate.CreateParameter("@Amount",adNumeric,adPa ramOutput,0, iAmount)
param.precision=18
param.numericscale=0
cmdUpdate.parameters.append param
cmdUpdate.Execute ,,adexecutenorecords

I have also tried retrieving/assigning the values after execution, as
follows, but to no avail:

cmdUpdate.CommandText = "myProcedure"
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@RETURN_VALUE",
adInteger, adParamReturnValue,0)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@OrderID",
adInteger, adParamInput,, iOrderID)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@First",adVarChar,adPar amOutput,80)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@Second",adVarChar,adPa ramOutput,80)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Third",
adVarChar, adParamOutput, 80)
set param =
cmdUpdate.CreateParameter("@Amount",adNumeric,adPa ramOutput,0)
param.precision=18
param.numericscale=0
cmdUpdate.parameters.append param
cmdUpdate.Execute ,,adexecutenorecords

sFirst =cmdUpdate("@First")
sSecond=cmdUpdate("@Second")
sThird=cmdUpdate("@Third")
iAmount=cmdUpdate("@Amount")

Is there some kind of limitation in ASP that you can only retrieve one
output parameter from a stored procedure? If not, how do I rectify this?
Thanks in advance.

Bari

Nov 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.