There are definitely occasions where stored procedures really don't cut it because the logic involved in the query is much better handled in an environment that can deal with the logic better. These are fairly rare and it's usually where the query needs to be altered depending on the inputs provided. While it is possible to have multiple stored procedures and for the application to choose which one to use the number of possible permutations can make this a poor option as can query efficiency options. In any case when I come across a dynamically created query and it is not executed as a paramaterised query I will go postal on the developer - there is no excuse for allowing a route for SQL injection attacks into a system when the fix was developed a couple of decades ago.

An advantage of application defined queries is that all of the application logic is within the same environment and the same version control system. This can be particularly important because version controlling (and just identifying the version of a stored procedure or function) is thoroughly painful in MS-SQL. Expanding version control systems and application deployment systems to include other environments is an important component of the application process but in general is woefully behind requirements without manual intervention added.

On the other hand, as soon as these stored procedures (and udfs) are considered an API then there are very strong arguments to keeping such logic closer to the database where changes to the database structure can be abstracted away from the API to some extent and the efficiency of the stored procedures and functions can be dealt with closer to the source.

I'd tend towards the stored procedure and function route where possible even though the application deployment and version control is considerably harder. Like much of life, and development, there is no 100% rule though.