returning result sets from stored procedures 
Author Message
 returning result sets from stored procedures

The books I have don't describe very well how to return a result set
from a stored procedure.  They show that all you have to do is put a
SELECT statement into your procedure.  However, how do you handle the
following cases:

1.  You have a select statement in your stored proc that you DO NOT
want to return, because you are just selecting data to be used as
local variables.  Does SQL Server ignore all selects of the format
with equals signs in them like ...

What if you have a mixture of fields like

Will mycol1 be returned to the client while mycol2 is not?

2.  Does every select send metadata?  What if you want to send a
single result set from multiple select statements?  How do you get rid
of the metadata in between rows?



Sun, 05 Sep 2004 23:34:55 GMT
 returning result sets from stored procedures



Quote:
> The books I have don't describe very well how to return a result set
> from a stored procedure.  They show that all you have to do is put a
> SELECT statement into your procedure.  However, how do you handle the
> following cases:

> 1.  You have a select statement in your stored proc that you DO NOT
> want to return, because you are just selecting data to be used as
> local variables.  Does SQL Server ignore all selects of the format
> with equals signs in them like ...


    yes... but you can use SET NOCOUNT ON at the begining of your procedure,
it's safer...

Quote:
> What if you have a mixture of fields like

> Will mycol1 be returned to the client while mycol2 is not?

    this case is not allowed by sqlserver.

Quote:

> 2.  Does every select send metadata?  What if you want to send a
> single result set from multiple select statements?  How do you get rid
> of the metadata in between rows?

    ???

Jean-Marc



Mon, 06 Sep 2004 01:16:03 GMT
 returning result sets from stored procedures

Quote:
> The books I have don't describe very well how to return a result set
> from a stored procedure.  They show that all you have to do is put a
> SELECT statement into your procedure.  However, how do you handle the
> following cases:

If you're using 8.0 you may also want to check table-valued functions.

Quote:
> 1.  You have a select statement in your stored proc that you DO NOT
> want to return, because you are just selecting data to be used as
> local variables.  Does SQL Server ignore all selects of the format
> with equals signs in them like ...

> What if you have a mixture of fields like

> Will mycol1 be returned to the client while mycol2 is not?

Why would you select mycol1 if you don't use it? Yes it will fail (see other
reply).

Quote:
> 2.  Does every select send metadata?  What if you want to send a
> single result set from multiple select statements?  How do you get rid
> of the metadata in between rows?

Use a union. Or in your application don't retrieve the column header
information (which is
also the solution for all your answers:-).


Mon, 06 Sep 2004 02:48:24 GMT
 returning result sets from stored procedures

Quote:
> > 1.  You have a select statement in your stored proc that you DO NOT
> > want to return, because you are just selecting data to be used as
> > local variables.  Does SQL Server ignore all selects of the format
> > with equals signs in them like ...

> > What if you have a mixture of fields like

> > Will mycol1 be returned to the client while mycol2 is not?

> Why would you select mycol1 if you don't use it? Yes it will fail (see
other
> reply).

umm ... I guess to return data to the user in a result set?  But if you are
saying
that this is an invalid TSQL statement, I guess it's a moot point.

Quote:
> > 2.  Does every select send metadata?  What if you want to send a
> > single result set from multiple select statements?  How do you get rid
> > of the metadata in between rows?

> Use a union. Or in your application don't retrieve the column header
> information (which is
> also the solution for all your answers:-).

Well, in my opinion, if I could get the entire result set in a union, I may
not even
need a stored procedure.  My thinking was that the power of returning result
sets from stored procs would be to do some processing in between selects,
and
return the result set in a piece by piece fashion.  Also I may be missing
something,
but I really don't want the  application to waste time retrieving lots of
column headers,
especially if, lets's say, each line in the result set is created by a
separate select
statement in the stored proc, it seems this would be extremely inefficient.
It would
also mean I would have to use an API that could handle multiple results sets
from
the same stored proc call.  Yes, I know ADO can do this, but it seems like a
strange
idea - if I really wanted to see multiple result sets, I would just write
multiple stored
procedures that each return a single result set.

Another reply stated to my message stated:

Quote:
> you can use SET NOCOUNT ON at the begining of your procedure, it's

safer...

This doesn match what I read. I thought that SET NOCOUNT ON is only for
insert/update/delete queries and it turns off the "Number of records
affected ..." message.
Stored procedures that return results sets don't send a count, do they?
Don't they just
send records?  Or does SET NOCOUNT ON have a different effect for result set
procs.

I guess I need to get an evaluation copy of SQL Server to try all this stuff
myself,
so I won't have to discuss this stuff from a theoretical point of view.  :-)

Thanks for the help!



Wed, 08 Sep 2004 06:10:09 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. return result set from stored procedure

2. return result set from stored procedure

3. Return result set from stored procedure

4. Return result set from stored procedure, possible ?

5. Returning Result Sets from Stored Procedures?

6. Returning result sets from stored procedures...

7. returning result sets from stored procedures

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

9. return multiple result sets from a stored procedure

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

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

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


 
Powered by phpBB® Forum Software