How not to consider all of the parameters in the WHERE clause of an IB procedure
Posted on 2003-03-28
how can i arrange a WHERE clause in a database select procedure in Interbase, if not all of the values of the input parameters should always be considered ?
Example: i have 3 input parameters: year, account_no and color. Sometimes i want to return only the records which match the given color (the other two parameters aren't important) - so the WHERE part of the sql statement would look like "WHERE table.color = :color"; on the other hand, sometimes i need also to consider the value of the year parameter -> the where part is: "WHERE table.color = :color AND table.year = :year". As you can see i should be able to consider any possible combination of the parameters.
In Delphi i can easily do that, because i build an sql statement dynamically (in a string) and then assign it to a IBQuery. Is it possible to do the same (or similar) thing also in IB ( to dynamically change the content of a WHERE clause ), or is the database procedure somehow "hardcoded" and you can't assign a string to the WHERE clause (the string could also be an input parameter)?
The result i want to achieve is to have only one procedure (or better, only one select clause) for all possible combinations of the input parameters. The example presented here has 3 parameters, but in my application i have 10 of them, so it would be practically impossible to write an select clause for every possible combination of the parameters.
Is it maybe another way to do that?