I would adopt a pragmatic approach - historically the primary 'benefit' of keeping business logic in stored procs is for performance reasons (2.5 tier architecture), whereas separating the business logic into a BLL tier (3/N tier) is generally cleaner from a maintenance perspective, and easier to test (Mock / Stub out the data access).
However, given that LINQ-enabled .NET ORMS such as LINQ2SQL, EF and NHibernate now create parameterised SQL queries, where query plans can be cached, are escaped for SQL Injection etc, I would guess that the move toward 3/N tier architecture is more compelling than ever, and most of the SPROCs (especially query-centric ones) can be avoided altogether. Repository patterns in .NET commonly expose IQueryable / accept Expression tree parameters, allowing for a type safe, yet flexible access to your tables. (Personally in SOA type architectures, I wouldn't expose IQueryable beyond the BLL, i.e. your Service and Presentation tiers should work with a well defined set of methods. Reason is that otherwise you can never fully test your system, and you won't sleep well at night knowing that some arbitrary query issued by a client could hit your DB without hitting indexes etc)
However, in a decent sized system, there will always be a few exceptions, where a really data intensitive piece of code might still need to be written as a Stored Proc for performance reasons. In these instances I would keep the SPROC, and expose the SPROC through the ORM, but still expose the function as a pass-through method on your BLL.