I think of the database as a completely separate thing from the application. It contains all the data, and it should protect that data. One way to do it is by having the database decide how people retrieve and manipulate data. I find that to be easiest
with stored procedures. It's pretty much the same as having a class decide what it tells other classes about its private data through functions and properties.
I also find stored procedures convenient from an abstraction perspective. I can alter the entire database diagram and still not have to change a single line in my application, just because the database allows me to GetPerson() instead of select specific columns
from a specific table based on specific values in specific columns in other specific tables. My application doesn't care how a database stores its stuff internally; it just wants data about a person.
It's a way of decoupling the code from your database implementation. Again, much like the way classes abstract their internal workings through their public interface. I can likewise completely rewrite a class's implementation and it'll still work with all my
existing code. It's good OO.
I personally don't care about the performance implications, because I'm not noticing any.