Stored Procedure Output Parameters

Discussion in 'ASP General' started by Bari Allen, Sep 17, 2004.

  1. Bari Allen

    Bari Allen Guest

    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
     
    Bari Allen, Sep 17, 2004
    #1
    1. Advertising

  2. 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.
     
    Bob Barrows [MVP], Sep 17, 2004
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andy in South Jersey
    Replies:
    3
    Views:
    2,544
    Andy in South Jersey
    Feb 11, 2009
  2. Andy in South Jersey
    Replies:
    0
    Views:
    303
    Andy in South Jersey
    Feb 11, 2009
  3. Andy in South Jersey
    Replies:
    0
    Views:
    344
    Andy in South Jersey
    Feb 11, 2009
  4. Andy in South Jersey
    Replies:
    0
    Views:
    274
    Andy in South Jersey
    Feb 11, 2009
  5. stjulian
    Replies:
    1
    Views:
    195
    Bob Barrows [MVP]
    Feb 8, 2006
Loading...

Share This Page