After seeing Michael Green's answer, which is full of great points, I wanted to provide some additional thoughts to weigh against his points. I'm not trying to win the argument for the side of using stored procs, per se, but I do believe they are often misunderstood and/or misrepresented in these kinds of conversations.
A) Scale - SQL statements have to be run whether you store them in Stored Procs or in a middle tier. So CRUD operations aren't going to scale any better which is probably 99% of what we're talking about here. But for real business logic that isn't just simple CRUD, yes you should generally not put that in the database unless you have a good reason to do so. And there are good reasons at times, such as to absolutely prevent bad data or for additional security.
B) Cost - I can't argue with this one in theory, but I can argue the hell out of it in practice. One should not assume that moving code from the SQL Server to a middle-tier necessarily equates to offloading work off the SQL Server. Often developers will just work with what they have to get their job done. If they need to get 20 records for a page and all they have access to is a method that gives them 1 record at a time, they will probably just use that and let it call the database 20 times before they'll think to say "hey, can I get a new method to get all the records I need in 1 go?" That's just a very simple example to get the point across, but this can show up in many ways. Different teams give their developers different levels of access across the stack so I undestand you may or may not relate to that specific example for your team; hopefully you get the point though. Having SQL in Stored Procs also opens the door to letting DBAs do what they do best to help you get the most performance out of your database. Putting it all in a middle tier closes that door and now you're more often relying on developers, who are rarely SQL experts, to tune your queries.
C) Reuse - I have to say I don't understand the point of the statement "Logic embodied in a stored procedure cannot simply be linked into, say, a stand-alone mobile app". If the logic is in a stored procedure, then there is no need to do so; so it sounds like this is saying "you can't do this thing with stored procedures that you don't need to do with stored procedures". Granted, I may be (probably am) grossly misunderstanding the use case scenario here so I welcome comments to explain because if I don't get it then probably many others don't either.
D) "Changing the SP affects every application which uses that DB, whether that application is ready for the change or not." If your middle tier is accessed through a service then this issue is not truly mitigated; you've just moved the same issue to your middle tier. However, if your middle tier is distributed (i.e. you copy a dll into each consuming project to use) then you can put off updating individual projects... and that opens up a whole 'nother can of worms. Like if you need to do a hotfix on an application that hasn't been updated now either you have to get an older version of code to make that fix to and manage a separate branch of code or you have to bite the bullet and update the application to work with the latest middle-tier code. Of course this where a middle-tier proponent would offer a solution where he has, wait for it..., multiple versions of a method! And now you're back to square one. I'm not saying one way is better or worse than another. I'm just saying that there are issues to solve either way you do it and you should consider what will work best for YOUR specific environment.
E) Tooling - "There are more, and more sophisticated, languages, tools and techniques available for development in the application tier than there are in the database (from comments, with thanks)." The flip side is that there are very sophisticated tools and talented individuals who can tune your database when they have access to the stored procs. They can find bottle necks and fix them, but that job because a heck of a lot harder when the sql is in the middle-tier and the SQL expert can't fix the the code your developer wrote which is generating crappy SQL. So again, it's a trade off. On this one, my opinion is to consider who is on your team and let what they are most comfortable with be prioritized highly when making a decision because it can DRASTICALLY affect development time which for you may outweigh the cost of your server.