Gail's article a few month's back on catch-all queries covered this territory really well:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries

Sunil's method is acceptable on small tables, but on larger tables this will choke a server. I think developers implement it in their test environment where tables have a thousand rows, and it does what it's supposed to do and returns quickly. So it goes to production where the million row tables are first encountered. That's when the stored procedure that runs in under a second during testing takes hours in actual use. Clustered index scan fun at its best. 😛