Subject Re: Select procedures
Author Cardenas, Mike
For my two cents, we use procedures with shh!(sql server) that do nothing
but execute other procedures depending on the params passed to them and this
one procedure returns various result sets depending on what was called.
From delphi, we just load the input params, there are no output params, and
execute the procedure( this is usually in the middle tier and we just make
the Clientdataset attached to it active).It would be nice not to lose this
type of functionality in a switch to IB as this allows most of the work to
be done on the server and significant reuse of procedures. The results from
this one procedure call can be a single customer record or years worth of
sales data, it doesn't matter to the client, it just displays the data.

Example:(may not be syntactically correct, but it illustrates the process).

create procedure proc1(@a varchar(1)='b', @b varchar(1)='b', @c varchar(1))
if a = 'b'
if b = 'b' and c is not null
exec @status = sp1 @b, @c
exec @status = sp2 @b
if a = 'c'
if b = 'a' and c is not null
exec @status=sp3 @b, @c
exec @status=sp4 @b
exec @status=sp5 @a, @c