Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    14

    Unanswered: Returning Multiple record sets from a stored procedure.

    Hi,
    How can we return multiple recordsets in a stored procedure.
    Can i have a sample piece of code on how to do this if this is is possible .

    Thanks,
    Deepak

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Returning Multiple record sets from a stored procedure.

    In the SP header, you have RESULT SETS option to say how many result sets you intend to pass back to the call

    Cheers
    Sathyaram

    Originally posted by bgdeepak
    Hi,
    How can we return multiple recordsets in a stored procedure.
    Can i have a sample piece of code on how to do this if this is is possible .

    Thanks,
    Deepak
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    İstanbul
    Posts
    19

    Re: Returning Multiple record sets from a stored procedure.

    Should be something like this :

    CREATE PROCEDURE EXAMPLESP (IN examplevar1 INT, OUT examplevar2 INT )
    RESULT SETS 2
    LANGUAGE SQL
    BEGIN
    DECLARE cur1 CURSOR WITH RETURN FOR <select .....>;
    DECLARE cur2 CURSOR WITH RETURN FOR <select .....>;
    .....
    OPEN cur1;
    OPEN cur2;
    END

    Like Sathyaram said, RESULT SETS <num> determines how mant result sets will be returned to the caller.
    Kezban

  4. #4
    Join Date
    Dec 2003
    Posts
    3

    Question

    Thanks for your help on the multiple recordset being returned by a DB2 stored procedure. Now Im facing issue with using this stored procedure in my ASP page through ADO.

    Here is a sample of we are tryring to do:

    Set oCmd.ActiveConnection = objConn
    oCmd.CommandType = adCmdStoredProc
    oCmd.CommandText = "myProc.PROC1"
    Set objRS = oCmd.Execute
    If Not objRS.EOF Then
    strArrayDetails = objRS.GetRows
    Set objRS = objRS.NextRecordset
    If Not objRS.EOF Then
    strArrayResponse = objRS.GetRows
    End If
    Set objRS = objRS.NextRecordset
    If Not objRS.EOF Then
    strArrayOther = objRS.GetRows
    End If
    End If

    The stored proc is returning 3 result sets. The number of rows in the result sets 2 and 3 are correct but their contents are not. Im getting 3 characters in each field and junk white space characters are appended to the the 3 characters.
    We executed the procedure in stored proc builder and we see the correct data. The problem is happening only if we try to display the result sets' contents through ADO in VB/ASP.

    Any help ASAP is greatly appreciated,
    TIA,
    asha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •