return multiple result sets from a stored procedure

return multiple result sets from a stored procedure

Post by Mark Stanle » Sat, 24 Jun 2000 04:00:00



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.

 
 
 

return multiple result sets from a stored procedure

Post by Alex Glauberzo » Wed, 28 Jun 2000 04:00:00


Mark,

Any application is going to see stored procedure as just another table,
though if your recordsets will have the same number of columns with the same
attributes
you can append them to the same temporary table and do final select from
this table.

Alex Glauberzon  /MCSE,MCT,MCDBA/


Quote:> 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.


 
 
 

return multiple result sets from a stored procedure

Post by Alex Glauberzo » Wed, 28 Jun 2000 04:00:00


Mark,

I just tried and yes you may put multiple select statements into the same
stored procedure and this stored procedure will return multiple result sets.

Alex Glauberzon /MCSE,MCT,MCDBA/


Quote:> 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.

 
 
 

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

In a stored proc, if I have a loop and inside the loop do a select, every
select will be returned as a seperate result set.  How can I combine all of
the result sets into one, or better yet, keep selecting into the same result
set?

Example code:




BEGIN


END

This will result in 10 result sets.  But I'd like 10 rows in a single result
set.  Any ideas?
With your solution in mind, what will happens in the case when one of the
result sets from above doesn't result in any rows, but others do?

Thanks,
Chris

2. Software Engineer,Pleasanton,CA

3. Returning Multiple Result Sets for Java Stored Procedures

4. Printing on D3/NT

5. Stored Procedure/Multiple Return Results/Set Statement

6. TCursor::cMax() returns String

7. multiple result sets returned from a stored prodedure

8. SEEKING PART-TIME DBA ROLE (New England Area)

9. returning result sets from stored procedures

10. Returning result sets in oracle stored procedure (OLEDB)

11. Can't update result set returned from stored procedure using RDO

12. Returning result set from function to stored procedure is very slooooooooow

13. best way to return a paged result set from a stored procedure