>>Ivan Wrote:>
>>What about this
>> where (D.SuplCde BETWEEN :ISupplierCodeMIN AND :ISupplierCodeMAX)

and ...

>I wrote:>
>I think the trick here will be to convert each and every parameter to
>the range option, and see if FB is then able to choose the appropriate
>index... I'll give the a bash and let you know how it goes.

Let's take a simplified example: I have a table for Stock Supplied by
various suppliers.
I want the same SP to return either all suppliers for a stock item, or
all stock items for a supplier.

So I implement this:
where (D.SuplCde BETWEEN :ISupplierCodeMIN AND :ISupplierCodeMAX) and
and (D.StkCde BETWEEN :IStockCodeMIN AND :IStockCodeMAX)

Now, most users will have 3 or 4 suppliers per stock item, but hundreds
of stock item by supplier, so this stored proc will ALWAYS make use of
the StockCode index, as that one has the highest selectivity? (Because
plans are decided before param values are set?)
I don't suppose I can specify the plan/index to use inside the stored
proc can I? Time to go collect Helen's book from the office I think!
Perhaps some kind of self built SQL inside the stored proc is the way to
go? Or is that not possible with selectable statements? Could be a
nightmare to debug to..