Stored Procedure Output Parameters

B

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
 
B

Bob Barrows [MVP]

Bari said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,754
Messages
2,569,520
Members
44,996
Latest member
rainocode

Latest Threads

Top