I have written TSQL that changes the entire query (which was on the order of 80 joins) dynamically based on about ~20 different parameters, only a few actually passed into the stored procedure, the rest based on settings and configuration. It took a few hours to grok how everything came together at first, but a few tricks that the optimizer will remove means you can add arbitrary conditionals in whatever order you please.
The example you give is VERY trivial for dynamic SQL.
postgres - http://www.postgresql.org/docs/9.1/static/ecpg-dynamic.html
mssql - https://msdn.microsoft.com/en-us/library/ms188001.aspx