Retrieving a recordset and Output parameters from a Stored Procedure

Discussion in 'ASP General' started by stjulian, Feb 8, 2006.

  1. stjulian

    stjulian Guest

    If inside a stored procedure, there a SELECT statement to return a recordset
    and another SELECT to set the value of an output parameter (as in SELECT
    @OutValue = Name FROM table WHERE pkid=5), would 2 execute statements be
    needed to return the OUTPUT parameter?

    Like this?


    adocmd.CommandTimeout = 120
    adocmd.ActiveConnection = conn
    adocmd.CommandType = adCmdStoredProc
    adocmd.CommandText = "dbo.sprMyProcedure"

    ' write to database using spr
    With adocmd

    set param = .CreateParameter("@Value1", adInteger, adParamInput, 4, 15)
    .parameters.append param
    set param = .CreateParameter("@Value2", adInteger, adParamInput, 4, 19)
    .parameters.append param

    set param = .createparameter("@OutValue", adVarChar, adParamOutput, 50)
    .parameters.append param

    set rs = .execute
    .execute
    MyValue = .Parameters("@OutValue").Value






    And, for that matter, how would I open the recordset so that it may be
    restarted with

    rs.MoveFirst

    without getting

    error '80040e18'
    Rowset position cannot be restarted.

    and, as well be able to retrieve the number of rows with rs.Recordcount ?



    --
    Thank you,

    Julian
     
    stjulian, Feb 8, 2006
    #1
    1. Advertising

  2. stjulian wrote:
    > If inside a stored procedure, there a SELECT statement to return a
    > recordset and another SELECT to set the value of an output parameter
    > (as in SELECT @OutValue = Name FROM table WHERE pkid=5), would 2
    > execute statements be needed to return the OUTPUT parameter?
    >
    > Like this?
    >
    >
    > adocmd.CommandTimeout = 120
    > adocmd.ActiveConnection = conn
    > adocmd.CommandType = adCmdStoredProc
    > adocmd.CommandText = "dbo.sprMyProcedure"
    >
    > ' write to database using spr
    > With adocmd
    >
    > set param = .CreateParameter("@Value1", adInteger, adParamInput,
    > 4, 15) .parameters.append param
    > set param = .CreateParameter("@Value2", adInteger, adParamInput,
    > 4, 19) .parameters.append param
    >
    > set param = .createparameter("@OutValue", adVarChar,
    > adParamOutput, 50) .parameters.append param
    >
    > set rs = .execute
    > .execute
    > MyValue = .Parameters("@OutValue").Value
    >


    >
    >
    >

    Two things:
    1. Your stored procedure should have a SET NOCOUNT ON statement in it to
    supress the informational "x rows affected" messages that are returned as
    resultsets.
    2. Neither output nor return values are sent to the client until all
    resultsets generated by thhe procedure are consumed by the client.

    I find that using GetRows (in conjunction with NextRecordset if multiple
    recordsets are returned) is an ideal way of dealing with this restriction

    set rs = .execute
    if not rs.eof then arData = rs.getrows
    rs.close: set rs = nothing
    MyValue = .Parameters("@OutValue").Value
    conn.close: set conn = nothing

    You now have a two-dimensional array containing your data - no need for the
    wasteful MoveFirst.

    You should go to msdn.microsoft.con/library and do a search for getrows to
    see the documentation.
    Also go to www.aspfaq.com and search for getrows to see a couple examples of
    using a getrows array.


    --
    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], Feb 8, 2006
    #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,516
    Andy in South Jersey
    Feb 11, 2009
  2. Andy in South Jersey
    Replies:
    0
    Views:
    293
    Andy in South Jersey
    Feb 11, 2009
  3. Andy in South Jersey
    Replies:
    0
    Views:
    337
    Andy in South Jersey
    Feb 11, 2009
  4. Andy in South Jersey
    Replies:
    0
    Views:
    269
    Andy in South Jersey
    Feb 11, 2009
  5. PinkGuava

    Retrieving Output Parameters and Recordset

    PinkGuava, Sep 16, 2004, in forum: ASP General
    Replies:
    1
    Views:
    397
    Bob Barrows [MVP]
    Sep 16, 2004
Loading...

Share This Page