limiting the number of record sets returned by a stored procedure

Hi,
I know that a stored procedure will return as many results sets as
SELECT statements are in. I want to actually mark which selects should be
returned as results sets and which are just internal. Is there a way of
doing this?
Thanks,
George.George,
There is noway to do this in t-sql. Why are you doing exactly?
if you are doing this kind of operation:
select col1, col2, ..., coln
from table1
where col1 like 'microsoft%'
if @.@.rowcount > 0
...
you can use:
if exists(select * from table1 where col1 like 'microsoft%')
...
AMB
"George Tihenea" wrote:

> Hi,
> I know that a stored procedure will return as many results sets as
> SELECT statements are in. I want to actually mark which selects should be
> returned as results sets and which are just internal. Is there a way of
> doing this?
> Thanks,
> George.
>
>|||Thanks,
Here are some more details. I have a stored procedure like this:
// start of stored proc, then
....
SELECT c1, c2, c3 from ...
WHERE (condition here)
if ( @.returned_rows > 0 ) return 0; /* all ok return*/
/* let the flow continue */
select c1, c2, c3 from...
where (a different condition here)
if ( @.returned_rows > 0 ) return 0; /* all ok return*/
/* let the flow continue */
......................
return 0 /* did not find anything*/
/// end of stored procedure
All this worked ok and I can get the result set from my OLEDB middle tier
using multiple results sets. Of cause I will always get ONLY ONE result set
but OLEDB needs to use the template with multiple results sets to work...
then I had to modify the stored precedure to do an INSERT before finishing.
Here is a scheleton code:
// start of stored proc, then
....
SELECT c1, c2, c3 from ...
WHERE (condition here)
if ( @.returned_rows > 0 ) go to FINISH /* all ok return*/
/* let the flow continue */
select c1, c2, c3 from...
where (a different condition here)
if ( @.returned_rows > 0 ) go to FINISH; /* all ok return*/
/* let the flow continue */
......................
FINISH:
INSERT INTO ....
VALUES (...)
return 0
/// end of stored procedure
The problem is this INSERT. For some reasons I cannot understand, my OLEDB
consumer templates will think that there are 3 result sets instead of 2! And
when I try to read the last one it will just crash while binding the
columns, which is normal because this last result set is bogus!!?
Thanks,
George.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F7B5BE46-FCA1-485B-902C-9F8D37629C78@.microsoft.com...
> George,
> There is noway to do this in t-sql. Why are you doing exactly?
> if you are doing this kind of operation:
> select col1, col2, ..., coln
> from table1
> where col1 like 'microsoft%'
> if @.@.rowcount > 0
> ...
> you can use:
> if exists(select * from table1 where col1 like 'microsoft%')
> ...
>
> AMB
>
> "George Tihenea" wrote:
>|||If you need these "other" select statements for debugging what I do is
add a debug variable to each stored procedure:
declare @.Debug int
and then in the TSQL code I test it:
if @.Debug = 1
Select * from Scheduler
Of course you can only run these from Query Analyzer interactively but
typically that is where you draw the line for debugging SPs.
On Thu, 17 Mar 2005 08:53:01 -0500, "George Tihenea"
<tihenea@.comcast.net> wrote:

> Hi,
> I know that a stored procedure will return as many results sets as
>SELECT statements are in. I want to actually mark which selects should be
>returned as results sets and which are just internal. Is there a way of
>doing this?
> Thanks,
> George.
>