SQL programming mistakes

I recently read this comprehensive list with ten common mistakes done when writing SQL. And I'd like to add number eleven to that list: using dynamic queries in stored procedures. Especially in projects with a lot of stored procedures for parts (or all) of the business logic there is often a few stored procedures with which builds an SQL query in a string and then executes it. These tend to be very difficult to understand and frankly are often only there just in order to return exactly what is needed. Personally I prefer a more naive way to solve queries that just have to be dynamic. There are three things you can do depending on exactly what makes the query complex. Either you can create a single query that returns everything you possibly need and then filter the data afterward. Or you can get what you need in a number of smaller queries. The last alternative I tend to avoid is to move the creation of the SQL out of the stored procedure and keep it as parameterized as possible.

All in all, in my experience the use of dynamic queries is usually not needed at all. Only with a bad database design may they really be needed,in which case you need to work around it as I described above. But the best way to avoid it is to make sure the design of the database supports the kinds of queries you need to do instead of relying on dynamic queries.