Subject Re: [firebird-support] How to use a an integer parameter to fetch parameter = "N" or all rows.
Author Svein Erling Tysvaer
Sorry for answering a bit late, I was put off by crossposting to several
lists (that's bad netiquette).

Seeing the answers in several lists, I'm surprised that no-one has suggested

WHERE ProjectID = :ProjectID
and LocationID = coalesce(:MyLocation, LocationID)

Of course, this doesn't use any index, but it is impossible to benefit
from any index if you at prepare time doesn't know whether or not to use
a parameter for the field in question.

I'm answering even though it is late because it puzzles me that no-one
suggested COALESCE. Is there something I have missed (like there being a
greater overhead to COALESCE than OR) or is this simply such an old
question that the standard answer developed before COALESCE was introduced?


mlq97 wrote:
> How can I easily query an integer column to get the rows matching
> an integer or also allow all rows to be fetched? EG:
> Item.Project (integer)
> Item.Location (integer)
> (Case 1)
> Select * from Item where
> Project = :ProjectID and Location = LocationID
> (Case 2)
> Select * from Item where
> Project = :ProjectID and Location = all locations
> Thanks
> Mitch