Stored Procedure Output Parameters
 

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 (sFirst) is returned (Input Parameter MyID 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,, MyID)
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,, MyID)
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

Re: Stored Procedure Output Parameters
 

Bari Allen wrote:
> 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.

Make sure you have a SET NOCONT ON statement at the beginning of the
procedure. Without this statement, the query will generate one or more "x
records were affected" messages and send them back to the clients as
resultsets. Since your ADO code never processes these resultsets, the output
parameter values are never sent. Use SET NOCOUNT ON to suppress these
messages.

Bob Barrows
--
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.