ummfish (8/17/2010)
I tried to use if-else and it worked. But the real query is much more complicated (two flags, one has two possible values for choosing tables, the other six possible values for choosing proper where-clause...) and there is no way to maintain the code if if-else is used.Any good idea to avoid the unnecessary reads?
Create a table ; put all the possible values and it's corresponding WHERE clauses in that (3 column in the table , one for value wit an INDEX, one for WHERE clause and one as IDENTITY column).
Based on your input value (which is going to be one value in your possible values) , pull its corresponding WHERE clause; build the dynamic query using that WHERE clause (i suppose that the SELECT clause and JOIN clauses (if any) will remain the same for all possible values) and execute it.
This way u eliminate the use of IF...ELSE or CASE constructs...