News Stay informed about the latest enterprise technology news and product updates.

Saying yes, no or maybe to stored procedures

The world of software somewhat resembles a pendulum. Styles of development swing in and out of favor. Take stored procedures, which work to some extent and may sometimes be the shortest way to a successful project.

This article originally appeared on the TheServerSide.NET.

The world of software somewhat resembles a pendulum. Styles of development swing in and out of favor. Sometimes that swinging is very slow -- so that it is practically imperceptible. Developers with considerable experience may not be aware of pendulum swings that occurred just a few years before they started their careers.

Take stored procedures. Middle-tier specialists tried mightily to avoid these rolled up bits of logic placed in the database. They did that because the general consensus after the first generation of client-server development was that stored procedures did not scale and led to vendor lock in. A lot of the impetus for the whole three-tier server-oriented movement came from disaffection with stored procedures.

Things look different to people who count themselves as database developers, of course. But, there is no black and white. There is no pure world, or pure middle tier. Stored procedures work, to some extent, and they may be the shortest way to a successful project, sometimes. The original great impetus to avoid them was vendor lock-in. In former times, there were quite a number of different data bases to get locked into.

The issue seems to have paled some as the number of mainstream data bases has diminished. We should always be mindful, and if we lock ourselves in, we should at least be conscious of the fact.

This particular opinion piece was 'triggered' by a recent meeting of the Boston .NET Users Group that I was privileged to attend. There, data base consultant Adam Machanic discussed his ideas about architecting effective data access, with an eye toward creating abstracting APIs between the database and the business logic tier. You could have SOA-style stored procedures, he inferred. Can it really ever be quite that gray?

Here is reaction from readers of TheServerSide.NET, with some minor grammatical edits:

  • "The majority of your business logic must be where you can easily debug it. Jumping from your application code to a stored procedure usually makes you lose your debug features. Same for unit testing and code coverage. This said, stored procedures are very useful and must be used when a compelling reason exists (performance in DB intensive operations, transparent data auditing, enforcement of complex and critical data level business rules). But SPs for CRUD operations is a big no-no." -- David Dossot
  • "….My reasoning for not using stored procedures in lots of situations, even when they seem like a good candidate to implement, is lack of processing support in terms of XML processing, lack of in-built version control, lack of sophisticated IDE to ease writing stored proc, lack of integration with other middle ware components, wherein you may need to talk to some other CORBA, EJB components, etc for your business logic and/or data needs or some other external business rules. So I always felt that I would be constrained in future if a need arose in my project to talk to some other component or application and then I would have a tough time to integrate my stored procedure logic and talking to external application…." -- Praveen Jhurani
  • "SPs are a technical feature like any other, [with] pros and cons. Limiting their use by some architectural model like "multi layer/tier application" is contra productive. Whether to host (business) logic in a database using a SP or not should driven by careful analysis of the problem domain and its non functional requirements like performance and scalability. Testability or vendor lock-in might also be of concern. So all in all it's a matter of meeting requirements on a case by case basis." -- Ralf Westphal
  • "….If you are writing an application where the requirements are to be multi-platform, then it may not make sense to take advantage of platform extension like stored procedures. If you are writing a line of business application, then do not create a false requirement, such as the application's need to be portable. It is not worth the cost in almost any circumstance. Too many developers create false requirements, like making reusable components and portability, when there is little to no chance your code for your line business application will ever be reused by anyone besides yourself….I understand ISV's and some corporate developers may have the goal of being portable so this argument depends on what you are building and why you are building it. There is no one right answer." -- Edward Ferron
  • "By far the most crucial need that stored procedures provide is an abstraction layer between the schema and applications that access the database. It is almost impossible to refactor the schema without it. That abstraction layer can be achieved through means other than stored procedures such as a windows service, web service or centralized data access library. However, in order to make any of those alternatives work, you must, absolutely guarantee that all access goes through your service layer. That is the rub. Achieving that type of control through just a centralized library for example in a medium to large organization is extraordinarily difficult [in my opinion] and certainly more difficult than with stored procedures. Beyond that, stored procedures are substantially easier to performance tune than dynamic SQL because of the SQL Profiler." -- Thomas Coleman

  • "To end all these long lasting threads, I've grouped a couple of these discussions and some of my articles about this in this blog post." -- Frans Bouma
  • Dig Deeper on SQL Server Stored Procedures

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.