How not to return multiple result sets from a stored procedure 
Author Message
 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



Mon, 11 Jul 2005 06:06:39 GMT
 How not to return multiple result sets from a stored procedure

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)



Mon, 11 Jul 2005 06:24:48 GMT
 How not to return multiple result sets from a stored procedure
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
Quote:
>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

>.



Mon, 11 Jul 2005 06:19:08 GMT
 How not to return multiple result sets from a stored procedure
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!



Mon, 11 Jul 2005 06:27:53 GMT
 How not to return multiple result sets from a stored procedure

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


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

> >.



Mon, 11 Jul 2005 07:14:33 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Returning Multiple Result Sets for Java Stored Procedures

2. Stored Procedure/Multiple Return Results/Set Statement

3. return multiple result sets from a stored procedure

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

5. multiple result sets returned from a stored prodedure

6. return result set from stored procedure

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

8. Returning a result set from a Stored Procedure

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

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

11. returning result sets/cursors from a stored procedure

12. Stored Procedures from Java/VB returning Result Sets(via ADO)


 
Powered by phpBB® Forum Software