<p>I’m listening to the Hanselminutes Podcast; "StackOverflow uses ASP.NET MVC - Jeff Atwood and his technical team". During the course of the Podcast they are speaking about SQL server and say something along the lines of 'The days of the Stored Procedure are over'. </p>

<p>Now I'm not a DBA but this has taken me a bit by surprise. I always assumed that SPs were the way to go for speed (as they are complied) and security not to mention scalability and maintainability. If this is not the case and SPs are on their last legs, what will replace them or what should we be doing in the future?</p>

On modern systems, parameterized queries are compiled and cached at the server, so they're just as fast as a stored procedure. They have most of the same security features as well.
For databases that serve a single application, it makes more sense to have the query logic there at the relevant location in the code.  If nothing else it makes it easier to do source control.  If you keep stored procedures on the server keeping track of them can quickly become a mess.  Additionally, if you're using an ORM tool you may not have much in the way of real SQL anyway.
If your database serves several different applications, then you may still want to use stored procedures to enforce business rules between application.