Some people have been discussing SQL Stored procedures lately. I figured I'd weigh in.
As with most things programming, it really depends on what you're trying to accomplish. There are certain situations when lots of stored procedures are appropriate (application designed to run on a single database, higher security, speed, etc), and certain situations where they are not (dynamic queries, multiple database types supported, etc).
The most helpful thing you can do in any type of programming is pick a pattern and stick with it! Real world however, it's just doesn't seem to work out that way- there's always some wierd case that pops in. When this happens you need to make sure to document it thoroughly.
At the end of the day, pattern or no, if your code is easy to read, understand, debug, and the application works; you have been successful.