Stored Procedure Output Parameters

[Bari Allen]

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,adParamOutput,80, sFirst)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@Second",adVarChar,adParamOutput,80, sSecond)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Third",
adVarChar, adParamOutput, 80, sThird)
set param =
cmdUpdate.CreateParameter("@Amount",adNumeric,adParamOutput,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,adParamOutput,80)
cmdUpdate.Parameters.Append
cmdUpdate.CreateParameter("@Second",adVarChar,adParamOutput,80)
cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("@Third",
adVarChar, adParamOutput, 80)
set param =
cmdUpdate.CreateParameter("@Amount",adNumeric,adParamOutput,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

 

[Bob Barrows [MVP]]

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