> > Unfortunately it doesn't work so well if you want to go
> > to the next logical step and have all of your data
> > selection via stored procs - it's not really possible to
> > write good select stored procs which handle multiple
> > possible filter parameters.
>
> I have had no trouble in this area, but maybe I don't provide filtering of
> result sets to the same extent that you do. Where I have provided this,
> I've tended towards implementing many short specific stored procedures
> rather than bigger, more generic ones.
>
> How many possible filter parameters are you providing for a single view or
> table?
>
We have a number of find screens which allow the user to enter some criteria
and then select from the list of matching records (eg Find Customer, Find
Quote, Find Invoice etc). As an example the Packing Slip selection stored
procedure takes 7 possible parameters, all of which are optional and some of
which require looking up other tables... eg
CREATE PROCEDURE PS_SLP_find
@slp_id t_id = null,
@inv_id t_id = null,
@slp_customer_ref t_string = null,
@cus_id t_id = null,
@cus_name t_string = null,
@slp_delivery_addr t_string = null,
@slp_not_invoiced t_boolean = 0
AS
... etc
Try writing that in a single fast SQL statement! As a way around it we use
IF statements to write optimal SQL for the most common parameters (and those
which have an index available) and write a generic (slow!) SQL statement at
the end for the less common cases. We are rarely talking about more than
about 100000 records so even in the rare worse case the stored procs run
fairly quickly (< 5 secs to return first 100 rows).
David.
DB Solutions Ltd.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
