Re: Multiple queries and stored procedures

["Wanko, Christopher G, CFCTR" <apollo@ATT.COM>]

> What the manual's statement means is that, if you have a
> stored procedure  that contains multiple SELECT's, only the first will be
> returned to SQR as a result set.
> For example, say you have a stored procedure:
>
>     /* contrived example */
>     create procedure p_get_customer as
>         select name, address, phone from customer where type='local'
>         select date=getdate()
>     go

Adam makes a good point with a good example, but executing a stored
procedure on a server is designed to save you some processing time on
complex statements.  With that in mind, it might be smarter and ultimately
easier on your consititution to
select your columns into output variables.  Doing so allows you to make as
many selects as you like, with the knowledge
that your data will be returned for all statements (assuming you having
multiple statements).

We do something similar here in my shop.  We have a cursor that loops over a
few tables and summarizes some results
for us; this is contained in a stored procedure.  We grab a building, pass
it to the stored proc, print the results, and loop back.
Not too hard, puts the SQL complexity and processing on the database where
it belongs, confines SQR to formatting.

-Chris