Retrieving Output Parameters and Recordset

Discussion in 'ASP General' started by PinkGuava, Sep 16, 2004.

  1. PinkGuava

    PinkGuava Guest

    Hi,

    I have a T-SQL stored procedure that returns both output parameters
    and a recordset. How do I retrieve them in my ASP script? As far as I
    know, the ADO Command object can be used to retrieve the output
    parameters, but will I be able to retrieve the recordset using the
    Command object as well? Or do I have to use the Recordset object?

    Example of stored procedure:

    CREATE PROCEDURE p_GetTestSites
    (
    @Proceed CHAR(1) OUTPUT,
    @ErrMsg VARCHAR(300) OUTPUT
    )
    AS
    BEGIN
    SET NOCOUNT ON

    /*
    some data processing done here
    */

    SET @Proceed = 'Y'
    SET @ErrMsg = 'Error message'

    SELECT TestSite FROM #TestSites

    RETURN 0
    END
    PinkGuava, Sep 16, 2004
    #1
    1. Advertising

  2. PinkGuava wrote:
    > Hi,
    >
    > I have a T-SQL stored procedure that returns both output parameters
    > and a recordset. How do I retrieve them in my ASP script? As far as I
    > know, the ADO Command object can be used to retrieve the output
    > parameters, but will I be able to retrieve the recordset using the
    > Command object as well? Or do I have to use the Recordset object?
    >


    You have to use both, keeping in mind that the all the records produced by
    the procedure have to be sent to the client before the output and return
    parameter values are sent. For a default server-side forward-only cursor,
    this means that the recordset essentially needs to be closed before you can
    read the values returned as output and return parameters. This is sort of a
    blessing, in that it encourages the use of methods such as GetRows and
    GetString to quickly process data.

    The technique looks like this:

    First, create a Command object (cmd), setting its CommandType and
    CommandText properties to point at the stored procedure, and creating its
    Parameters collection (using my stored procedure code generator, available
    at
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear,
    makes this job easier)

    Then, instead of
    cmd.Execute ,,adExecuteNoRecords

    You would do this:
    Set rs=cmd.Execute
    if not rs.eof then arData=rs.GetRows
    rs.close:set rs=nothing
    outputparmval = cmd.Parameters("@outputparm").value

    if isArray(arData) then
    'process the array
    else
    'return message indicating no records were returned
    end if

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Sep 16, 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. Replies:
    2
    Views:
    544
    bruce barker
    Jan 27, 2008
  2. Hung Huynh
    Replies:
    8
    Views:
    291
    Bob Barrows
    Sep 24, 2003
  3. Robert Mark Bram

    Too few parameters to RecordSet.Open?

    Robert Mark Bram, Oct 18, 2003, in forum: ASP General
    Replies:
    2
    Views:
    153
    Robert Mark Bram
    Oct 18, 2003
  4. Bruno Alexandre
    Replies:
    5
    Views:
    170
    Bob Barrows
    Mar 3, 2004
  5. stjulian
    Replies:
    1
    Views:
    176
    Bob Barrows [MVP]
    Feb 8, 2006
Loading...

Share This Page