Apoplexy

Posted by Jeremy Miller on February 9, 2006

Like many historically Microsoft development shops we struggle with grossly inappropriate usage of stored procedures (every single MS development shop I’ve worked in or consulted for had this issue to some degree or another).  Part of our “rules engine” varies by configuring the name of a stored procedure per customer for a certain data validation.  The sproc’s are edited and deployed outside of any kind of disciplined, consistent process.  We *really* want to get rid of these things, but in the meantime, it’s spawning some great internal quotes. 


“If we can’t get rid of the stored procedures, can you build us a ‘Stored Procedure Writer’ tool?” – Yes, we’ll call it Query Analyzer


“They’re just stored procedures, they can’t do any harm” – My jaw hit the floor.  It’s a running joke in the office now.


“It’s easier for my guys to write stored procedures than have to edit code somewhere” – that one might have been true in the past, but it was a sign in bright pink neon letters that the code *really* needed some serious love.


 


 


Now listening to and torturing my colleagues with:  What’s new Pussycat? by Tom Jones

About Jeremy Miller

Jeremy is the Chief Software Architect at Dovetail Software, the coolest ISV in Austin. Jeremy began his IT career writing "Shadow IT" applications to automate his engineering documentation, then wandered into software development because it looked like more fun. Jeremy is the author of the open source StructureMap tool for Dependency Injection with .Net, StoryTeller for supercharged acceptance testing in .Net, and one of the principal developers behind FubuMVC. Jeremy's thoughts on all things software can be found at The Shade Tree Developer at http://codebetter.com/jeremymiller.
This entry was posted in Ranting. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Jeremy D. Miller

    Eric,

    An external ORM tool like NHibernate pays big dividends for application structure and testability. We’re trying to retrofit a real domain model into our legacy code to replace the existing procedural “transaction script” architecture and I can already see an improvement in coupling and cohesion.

    I see a lot of developers crashing and burning because they keep their domain logic too intangled with the persistence. I’ve had several conversations lately with people that are using internal mapping “Active Record” approaches in .Net and every single one of them is struggling with transactional boundaries and dirty/new/deleted state management that is fairly simple with something like NHibernate.

    Keep in mind, when I’m talking about SPROC abuse I’m not talking about basic CRUD operations. I’m talking about business logic, communication outside the SQL box, and even user interface logic within a procedural sproc.

  • http://www.codebetter.com/blogs/eric.wise ewise

    nHibernate et al pay big dividends when the database structure is in a state of frequent change… I can’t say I come across that very often in my projects.

    What always makes me slightly nervous about things like nHibernate is that I’m injecting a non .net framework dependency into my project which can make upgrades etc trickier.

    Like all things development there is no silver bullet. Part of being a good developer/architect is analyzing the environmental factors present in your project and making appropriate decisions based on those factors, not on your personal preferences.

  • Liang

    It is pretty culture issue as Jeremy said. Your approach really made other persons unconfortable. Resisting changes is part of human nature.

  • http://dotnetjunkies.com/weblog/johnwood John Wood

    Some classic and mandatory reading on the subject of stored procs, a great post and commentary on Frans Bouma’s blog:
    http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

  • Liang

    Since I am using nHibernate in my current project, I am facing this situation all the time. Recently a Microsoft “Developer Evangelist” came to out site, telling us “stored procedure is a must”. He claimed not to use any codegen or ORM. I doubt if he writes any code. How a guy like to write tedious data access code to become a “Developer Evangelist”? My boss is non-technical guy. It seems that I really did a BAD thing to use nhibernate, instead of stored procedure.