This business of stored procedures is questionable. I had a talk with my friend the other day, who is a database administrator for a major credit card processing company, and he says that they don’t use them at all at their company. The reason is that they want to keep the business logic separate from the database logic. That makes perfect sense in their scenario. They’ve got a lot of developers who would have to have access to the database, or hand the task off to a dba. Neither of those sound like good solutions for them. Either, A.) You have a huge security risk by letting developers in, or B.) DBAs are doing meanial tasks, and they’re probably too expensive to have sitting around writing SELECT * FROM whatever statements all day.
He did say that stored procedures do have a major performance advantage, though in their scenario they don’t need to be using the database servers to run logic that could be run elsewhere. Of course everyone knows that stored procedures are fast, but it was good to hear it from a DBA .
I love modularizing logic, though. I love to be able to change an entire application by changing some code in a single file. I see the performance advantage but I understand it comes at the price of obscuring your business layer. For me, on this website, I’m using them. But at work, I’m still not quite sure if I’ll go that route yet.
All that said, this is a great resource.