I typically use sprocs only to avoid putting actual SQL in my C#/VB/whatever code, and I keep them as small as possible. They tend to be single select/update/insert/delete statements, and if an insert they're usually followed by select scope_identity.
But that's about all. All my logic is in other places.
If I need more code on the database level it's usually in triggers and check constraints, not stored procedures.