How not to return multiple result sets from a stored procedure

How not to return multiple result sets from a stored procedure

Post by Chris Becke » Fri, 24 Jan 2003 07:06:39



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

 
 
 

How not to return multiple result sets from a stored procedure

Post by Anith Se » Fri, 24 Jan 2003 07:24:48


Use a Number/Sequence table (or something which you can create on fly)
& do:

SELECT Number AS [CurrentPosition],
       'Test' AS [Name]
  FROM Numbers

If you can post your actual requirement along with a repro script someone
can show you how to avoid a loop and create a set based solution which
will return a single resultset.

--
- Anith
(Please respond only to newsgroups)

 
 
 

How not to return multiple result sets from a stored procedure

Post by Bob Barrow » Fri, 24 Jan 2003 07:19:08


If using SQL 2000, insert the record created in the loop
into a table variable, then, when the loop finishes,
select the records from the table variable.
If using an earlier version of SS, then do the same with a
temp table.

HTH,
Bob Barrows

Quote:>-----Original Message-----
>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
Quote:>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

>.

 
 
 

How not to return multiple result sets from a stored procedure

Post by Joe Celk » Fri, 24 Jan 2003 07:27:53


You need to write this as one SELECT statement and not use procedural
code.  Without the actual problem,it is hard to guess what tghis would
look like, but one common trick is to create a table of sequential
numbers and do joins to it:

SELECT seq AS current_position, 'Test' AS name
   FROM Sequence
 WHERE seq BETWEEN 1 AND 10;

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

How not to return multiple result sets from a stored procedure

Post by Chris Becke » Fri, 24 Jan 2003 08:14:33


Thank you Bob!!! That was the trick.  And thanks for the quick response!!


> If using SQL 2000, insert the record created in the loop
> into a table variable, then, when the loop finishes,
> select the records from the table variable.
> If using an earlier version of SS, then do the same with a
> temp table.

> HTH,
> Bob Barrows
> >-----Original Message-----
> >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

> >.

 
 
 

1. Returning Multiple Result Sets for Java Stored Procedures

Do you have any (or know of any) examples for returning Multiple Result
Sets from Java Stored Procedures?  I have implemented your example on your
web page, but we are not sure about how CURSORS map to java?  In your
example, you return a cursor from the stored procedure, what is the
equivalent java class?

Thanks in advance,

Blaine

2. I/O Buffer size for Solaris2.6

3. Stored Procedure/Multiple Return Results/Set Statement

4. Removing Pages & Page Frames

5. return multiple result sets from a stored procedure

6. brackets in text calcs

7. AS/400 v4r4 stored procedure is NOT returning result set to VB

8. Shrink LDF?

9. multiple result sets returned from a stored prodedure

10. return result set from stored procedure

11. Stored Procedures and Returning a Result Set (array)

12. Returning a result set from a Stored Procedure

13. OLE ,Oracle, ans Stored Procedures returning result sets