Parsing Multiple Result Sets from stored procedures

Parsing Multiple Result Sets from stored procedures

Post by Arthur Yousi » Thu, 07 May 1998 04:00:00



We use stored procedures heavily for processing and returning information.
Our stored procedures are complex and can return several result sets.  For
example,
name                    company                            area
-------------------      -----------------------------       ---------------
-----------
Sue Harvey          XYZ, Inc.                            Midwest
Jim Andersen      XYZ, Inc.                            Southwest
Melanie Smith     XYZ, Inc.                            Northeast

(1 row(s) affected)

Dept                     Position                        NumOfSubs
---------------------    -------------------------      -------------------
Accounting           CPA                             0

(1 row(s) affected)

The getMetaData() method picks 'name', 'company', and 'area' but doesn't see
the 'Dept', 'Position', and 'NumOfSubs' fields.  I'm new to database
programming and I'm not sure how to approach this.  The reason we use
multiple result sets is that we don't always have the same number of rows
and columns, i.e., the example above shows three employees in the same
department, position in different areas.  Any help would be greatly
appreciated.  Thanks.

Arthur Yousif

 
 
 

Parsing Multiple Result Sets from stored procedures

Post by Ken Smit » Thu, 07 May 1998 04:00:00


You probably reterived the first result set by doing:

rs = stmt.executeQuery();
while(rs.next()) {
    rs.getMetaData();
    process first resultset.....

Quote:}

To get to the next result set you must do.

stmt.getMoreResults();
rs = stmt.getResultSet();
while(rs.next()) {
    rs.getMetaData();
    process second result set.....

Quote:}

Ken

> We use stored procedures heavily for processing and returning information.
> Our stored procedures are complex and can return several result sets.  For
> example,
> name                    company                            area
> -------------------      -----------------------------       ---------------
> -----------
> Sue Harvey          XYZ, Inc.                            Midwest
> Jim Andersen      XYZ, Inc.                            Southwest
> Melanie Smith     XYZ, Inc.                            Northeast

> (1 row(s) affected)

> Dept                     Position                        NumOfSubs
> ---------------------    -------------------------      -------------------
> Accounting           CPA                             0

> (1 row(s) affected)

> The getMetaData() method picks 'name', 'company', and 'area' but doesn't see
> the 'Dept', 'Position', and 'NumOfSubs' fields.  I'm new to database
> programming and I'm not sure how to approach this.  The reason we use
> multiple result sets is that we don't always have the same number of rows
> and columns, i.e., the example above shows three employees in the same
> department, position in different areas.  Any help would be greatly
> appreciated.  Thanks.

> Arthur Yousif


  vcard.vcf
< 1K Download

 
 
 

1. return multiple result sets from a stored procedure

Does SQL only pass back once recordset from a stored procedure?

I would like to return multiple recordsets into ADODB and use the
Recordset.NextRecordset method to retrieve them into my application.

The syntax of Recordset.Open() shows multiple SQL statements, but I need the
return of multiple recordsets with the specification of only one SQL
statement(stored procedure).

My original design relied on passing a single parameter of dynamic criteria
and returning 8 recordsets... each one filtered with a temp table created
using the passed criteria.

Thanks.

2. Simple performance question, please ...

3. multiple result sets from a stored procedure

4. cue cards in sql server

5. How not to return multiple result sets from a stored procedure

6. OLE DB Client Interface

7. Stored Procedure/Multiple Return Results/Set Statement

8. ONLINE DOCUMENTATION APPLICATIONS

9. Returning Multiple Result Sets for Java Stored Procedures

10. Executing Stored procedures with multiple Result sets

11. Multiple Result Sets from DB2 stored Procedure