When to use Stored Procedures?

No coding blog would be complete without an overly-simplistic salvo fired into this bloody online battlefield, so here’s mine.

What if you’re writing an application that will make heavy use of an RDBMS?