The road to eternal burning hell is paved with stored procedures. My experiences (!!) make it so i will never be convinced on the risk:reward being worth it.
I get the distinct impression that many teams have very weak engineering rigor around their DBs, which leads to a lot of avoidable pain.