>Sasha Wrote:>
>The point is, the null is required in this scenario. As far as I understand Maya, this is what he wants:
>User has an option to filter the results for SupplierCode. If user selects specific supplier, he wants results only ?>for that supplier. But, if you want to NOT filter the results set for supplier, you pass in a null and you get >results NOT filtered.

Yes, that's exactly it ;-) (Except for the 'he' part, but you're forgiven - probably better to err that way, hey)

>Now, the problem is this way the query does not use indices (I haven't acctually tried, but I guess it doesn't).

In FB 1.5 it doesn't. Was hoping in a future release it would..

>IMHO, if you know your tables are not big, this pattern of querying is ok because it is very easy on the client side >to pass specific/all parameters. If performance is crucial, then dynamically construct your sql on the client side. >It's a mess and very hard to maintain, but you will get use of indices that way.

OK, here's the full scenario: I have about 170 of these stored procedures, for use as bases for different reports.
In the past, where I come across a really sticky one, and the SP is only 10 or so lines long, then I code as follows:

If :IInputParam is null then
.. do SQL without mentioning input param
Else
.. re-do same sql, but this time add where t.Field = :IInputParam

I am now working on a new SP - came across the same old problem again, so thought I'd have a bash at trying something different this time. Also, this new SP is going to work on one of the largest tables yet, as well as have 3 parameters, one of which will 99% of the time be NULL, but the NULL param could be any of the 3 quite logically.
It is also a very long select statement.
So, to use the same trick as previously, I'd have to expand a 100 line SP, to be a 300 lines SP, with almost identical code...hang on that wrong, is actually "3 to the power of 3" = 27 combinations, so that would be 2700 lines of code to ensure a customer does not come across a slow report.

If only FB could determine that a particular statement is always going to be true for a set parameter, and optimise that statement out of the enquiry before determining the plan...but I'm guessing the plan is determined BEFORE the parameter values are set?

Maya