Things like search interfaces might offer filters over multiple columns... your choices here:
1. Use an ORM
2. Write SQL for each permutation of filter
3. Write a helper that will compose the SQL based on the filters needed
And that 3rd one is the only time I stray from plain SQL towards something that looks like a lightweight abstraction over the SQL.
Similarly... stored procs, I avoid these but there are times when they are necessary. If you need the boundary of a transaction to span multiple SQL calls then it is safer to do so within a stored proc. Yes you can use a transaction and rollback, but from a maintainance perspective I've seen refactors break transactions that were not fully encapsulated in a single Go (insert other language) function .
Yes keep things simple with SQL and do everything in the application... but also break those rules rather than do something daft or with side effects.