You really can't avoid dynamic SQL if you need it for your software. For example, if you give your users the ability to select filters to apply to the data fetc

You really can't avoid dynamic SQL if you need it for your software. For example, if you give your users the ability to select filters to apply to the data fetched from the DB, you will have to dynamically create the SQL unless the number of possible filters is extremely low. Above a certain scale this might not be feasible for predictable performance, but not everything has billions of rows. The choice isn't to use dynamic SQL or not in these cases, it's whether you implement the feature that requires dynamic SQL or leave it out entirely.