Check out this article:
regarding Dynamic SQL. The author describes the technology, different options and basically a lot of insights from his experience.
And this article from the same author:
about dynamic search conditions (when we can filter a list of data using one or more filters.. basically every list report in backoffice systems requires this). Again, great information what can be done, how to do it and what to avoid or pay attention to.
This author has many other articles probably just as good about SQL related subjects.