Wow. It's been 3 years and still there is a lot of activity on this post. Thank you all very much for your replies.

Adding to what I said then, the conditional approach is probably not suitable for large data sets or systems where response is critical. Smart joins, dynamic SQL or even application - level constructed query statements would probably do the job better there. It's better to think of this specific approach as flexibility vs performance. You can choose what you need more.

What you get with this approach is a flexible WHERE clause generator - without the generator 🙂 Of course it's always possible to construct such a WHERE clause in other ways such as those mentioned above, but, in my opinion, it'd take more man-hours and it should only be done where it's worth it.