It all really depends on your usage of stored procedures and business requirements.
There are a number of projects that do use three-tier architecture and depending on the nature of business requirements you may need to shift some operations to data tier.
In usual case, for the given architecture, the middle tier or business services layer, consists of business and data rules. However, sometimes it makes big difference to shift heavy set base operations to be done in data tier - through set of stored procedures.
Thus, it is really a case-base approach which has trade-offs in itself. However, Microsoft design guidelines of Three-Tier Architecture Model recommends to keep your business logic in middle-tier.