This is probably a question that has been around for 20 years, but I'm going to ask anyway. I have a screen that has multiple search options. Some can be combined. Some are exclusive.
Ex:
Search by first and last name
OR
Search by age
What is the best way to handle this? Do I handle this in the app and either call 1 of many functions or 1 function with a whole bunch of if/else's. Doing a series of if/else's just seems so outdated. Isn't there a more efficient way?
On
I would do the validation of which fields can be completed together on the front end.
For the stored proc the main goal is to have no sql with concatonations in it.
In the sproc I tend to use a string of isnull statements for this to use the param or the field value if the param is null.
as you say, its not a new trick but it works and is a lot faster than generating new sql each execution. It also has easily predictable execution plan.
I recommend using a builder to gather the various predicates into an object, then pass that object to a function/method that generates the relevant query.
The SQLORM Java library uses this approach to build SQL queries in an object-oriented fashion. This page provides a good discussion of the pros and cons of building a query string using a method with a lot of if-else's, versus the builder approach.