At least for MSSQL: Never do this (before learning about query caches). Or at least, if you do, add (option recompile) to the query.
For each combination of parameters to search for you may want to use a different index.
But... the query plans are cached by query string lookup!
So it is imperative that your search string looks different for each query plan/index being used.
The code suggested here will pick a more or less random index (the one optimized for the parameters of the first execution) and stick with it for remaining executions, leading to bad queries for combinations of non-null that doesn't match the first query.
You could just add a comment inside the string that was different depending on what parameters are null, but that is no less complex than just generating the query.
PS: Of course there are situations where it fits, like if your strategy is to always use the same index to do the main scan and then filter away results from it based on postprocessing filters. Just make sure to understand this issue.
I debugged an app a couple of years ago that from time to time brought entire MSSQL down. The server had to be physically restarted. Nobody could figure out for years what was going on, all the queries had been analyzed and checked for missing indexes, everything was profiled... Except when an app generated a query like this which did not go fine through the cached plan.
For each combination of parameters to search for you may want to use a different index.
But... the query plans are cached by query string lookup!
So it is imperative that your search string looks different for each query plan/index being used.
The code suggested here will pick a more or less random index (the one optimized for the parameters of the first execution) and stick with it for remaining executions, leading to bad queries for combinations of non-null that doesn't match the first query.
You could just add a comment inside the string that was different depending on what parameters are null, but that is no less complex than just generating the query.
PS: Of course there are situations where it fits, like if your strategy is to always use the same index to do the main scan and then filter away results from it based on postprocessing filters. Just make sure to understand this issue.