30 May 2006
Stored Procedures 2.0?
Coming from a Microsoft platform background, I’m well versed in the stored procedure mantra, which practically equates to “use them everywhere”. Over the past year or so, I’ve starting questioning that assumption While stored procs are useful, they do more to add unneccessary complexity to application development.
bq. For modern databases and real world usage scenarios, I believe a Stored Procedure architecture has serious downsides and little practical benefit. Stored Procedures should be considered database assembly language: for use in only the most performance critical situations. There are plenty of ways to design a solid, high performing data access layer without resorting to Stored Procedures; you’ll realize a lot of benefits if you stick with parameterized SQL and a single coherent development environment.
I now longer use stored procs when working with an application database They become much more useful when dealing with an integration database Martin Fowler captures the [differences](http://martinfowler.com/bliki/DatabaseStyles.html.
The) real question to ask is, “Where am I spending my time?”. Am I doing application development where I can encapsulate my business logic into a single application layer? Or am I working with a database that multiple application must interact with where a clear business layer nearly impossible to maintain?
Though I still have a foot in both worlds, I have gravitated over the past few years toward much more application development. I simply enjoy it more. Hence, I find myself using stored procs less and less.