At 11:36 AM 23/09/2004 +0200, you wrote:

> > What about water-cooling the programmer? Why on earth would you want to
> > do
> > this? The mind boggles at the serpentine internals of it. Why not just
> > write a single stored procedure to get the set?
>
>Helen
>
>Now that you have sufficiently humbled me, please explain the "serpentine
>internals of it".
>It works well with a join, why does an inner join break it?

Did you mean to say "why does an outer join break it?" The outer join is
causing it to execute over and over just to (repeatedly) test and provide
conditions for the non-matches. "Serpentine" just means the thought of
what's happening in the box boggles the mind (and the CPU fan, apparently!!)

>The SP is very long and very complicated with one task only, to work out the
>figures for the specified period. Why is it illogical to call the same SP
>twice (or tens times) to get values for those periods?

Let's talk about "irrational" so we're not getting muddled up with logic
(since we don't know a thing about the logic). It's not irrational to call
the same piece of code multiple times (or recursively, if necessary), if
you do it all within the walls of a single SP call. Structurally, your
code (whatever it might be) will need some tidying so that the repeating
grunt work is done in inner calls that are not selects but execute
procedure calls with returning_values. Only the outer shell (the looper
that selects the controlling set and actually returns rows to the client)
would contain a SUSPEND.

What's irrational is doing it by *joining* the output of different calls to
the same SP using the same input parameters, causing millions of avoidable
churnings and roilings.

./heLen