Who Needs Stored Procedures, Anyways?

Eric wrote:

Naked SQL statements clumsily embedded in other languages use the same ‘big iron’ database languages. But wrapped up as text strings like many programmers do, they’re not debuggable at all, in any IDE.

Paul Lach wrote:

  1. Anyone that has had to research a proposed schema change will tell you what a nightmare it is to identify all of the code that could be affected by the proposed change. How do you know if you have found all of the code? Do you just wait and see what breaks after the change is made?

The answer to both of these problems is to have unit tests. It’s that simple. Once you’ve got your ‘dynamic’ SQL passing its tests, you know it’s right. Changed your schema and want to know if you found all the affected code? Run your tests.

You know your tests cover the affected code because you either write code test-first (no code is written until a failing test is), and/or you rely on a coverage tool, which tells you which bits of the system have been exercised.

With ADO.NET’s generated typed ‘DataSet’ classes, you even get to see compiler errors showing you where you broke your code when your schema changes. You can just browse your ‘task list’ and fix each problem in turn. To make this work, all you have to do is regenerate your DataSet classes.

Obviously this is no substitute for a full set of unit tests, but it’s useful nonetheless.


I believe that any DataLayer must be a simple code block, that they allow operations against DB.

That code block would not have to know on the Business Entities. Single to specialize it is to execute the operations (Store Procedures and SQL Sentences) against the engine DB (SQL, Oracle, DB2, etc.), with which this setting.

Finally, I invite to you to download the DataLayer.Primitives Public Version.

This is very cool Data Layer :slight_smile:

DataLayer.Primitives - Readme!

Anyone who says that T/SQL is archaic while still coding in ASP deserves a boot to the head. If you use Cold Fusion, Java, or C#, that’s another story.

SP is best for distinct logic building blocks with encapsulated business rules that can be treated by the developer as a black-box object (think OOP advantages).

In-line SQL is best for single queries that may additionally be cached at the app server level if ‘static-enough’.

Almost every ASP.NET project I have seen (where I wasn’t the architect :wink: ) the use of stored procedures has led to complete and total chaos. Part of the reason is that they tend to be there because the designers centered everything on the relational database design, making that the first priority, and part of the reason because no one can properly debug reams of bloody t-sql when you dont even know if the bug is in the t-sql or in the c#. In fact, when it comes to basic CRUD (but not complex report type or long running queries in many cases) I dont see any reason at all to use stored procedures and in my very good experience, it is dangerous to allow their use. Where this dogma about avoiding in-line sql has come from I don’t know, but it should be recognised as just that - dogma. Security? Performance? Pfa! My arse!

All this has made me remember one of my biggest wish items for stored procedures: interfaces.

I recently duplicated a large set of procs to provide my code with identical field names in the results they returned, e.g. “name” and “id”, vs. “entityname_name” and “entityname_id”.

First let me start out by saying I have been developing database application since DOS which I had to migrate many applications to a new platform. I have worked on both stored procedure base systems and embedded SQL applications. Performance was always better with stored procedures.

I personally do not feel the business logic should reside in the application, application have their rules and data has its rules. In today’s IT world we now have many interfaces for the same application. They are thin, rich, and/or smart clients. These clients can all use the same datasets for accessing systems.

Why would you put embedded SQL in every client? That does not sound that productive. I would have to redistribute the client. With a thin client the user would get the changes the next time they access but I still have to move my code from development environment to production. With smart clients I would have to distribute the application too. Can I have smart and thin clients accessing the same datasets? Yes, so why embedded SQL?

One other argument I have seen on the Internet about stored procedures was that it was difficult to diagnose when a problem exists. What I say to these people is you do not understand databases and need to go back to school. If you think it is better to download the data to the client and then process the data for some business rule you are crazy. The whole purpose of DBMS was to return only the records necessary to accomplish a task and save the throughput.

One other point to make about stored procedures; it is easier to manage security with stored procedures. So you see I can not see why any project would fail with stored procedures, unless you do not understand databases yet and can only do web pages.

We have not even discussed Cobb and Date’s theories of relational databases. I recommend you read and understand their theories before you start yapping about these theories. SQL engines are not true relational databases. What happens if we have to change the locations of data? Stored procedures would only have to be changed where embedded SQL would mean the application would have to be rebuilt and re-distributed.

Best Regard,

Bob Zagars
Senior Software Engineer

Has anyone heard of an ORM? I mean, seriously, this is 2006

ORM is the Vietnam of our industry:




I do not know why people say using procs are a headache. If you know what you are doing, it is not a problem. If you work in an environment that processes or manipulates millions of rows, you will know that you need procs in this case.
Also, I cannot imagine putting business rules in a client side app. It may be a good idea to create objects on an application server but if you do not take this approach then you need a database stored proc. Enterpise wide, corporations tend to share processes. If your rules are built into the client then you cant share anything. Reuse across the enterprise is important and not all departments use .NET
You must ofthen think globally across the enterprise.

If your rules are built into the client then you cant share anything. Reuse across the enterprise is important and not all departments use .NET. You must ofthen think globally across the enterprise.

So putting business rules in stored procs, and tying your enterprise to Oracle/Microsoft/Sybase in perpetuity… that’s a good idea?

How about putting those rules into web services or http REST services that work on any platform? Now that’s thinking globally across the enterprise.

Warning: rant on database (in)dependence ahead.

Over the past decade or so I’ve worked with several RDBMSs: Oracle, Postgres, MS SQL, DB2, Sybase, and a little MySQL (wasn’t a fan). If there’s one thing I’ve learned is that each database has a fundamentally different architecture, and database independence is NOT easy to achieve. I’m not even sure if it’s desirable.

Switching databases has always been a big, big deal in my experience. This coming from a guy who started on the completely opposite end of the spectrum. I did everything generically - this is just the perspective I was taught in college and literature (black boxing and all that). The first way I ever used a database was by querying MS-SQL doing “select * from mytable” and iterating through the results. I realized that was stupid and learned this whole SQL thing. I learned about ANSI SQL and how I should try to conform to this standard to achieve independence. Then I realized I was using the lowest common denominator and achieving poor results. And coding became much more difficult - had to check against the ANSI standard before using a particular feature: “Darn, x isn’t ‘ANSI’? Well, I’ll do it the hard way…”

If you just replace one database with another, plug-in style, you’re going to notice differences (perhaps “bugs”), even with the latest and greatest ORM. With the exception of trivial applications, these differences will affect your application, potentially drastically. For example, you have two users running a transaction on the same row(s) of data. Due to fundamental differences in concurrency mechanisms, Sybase might deadlock (and so one user is rolled back and gets an error) while Oracle runs smoothly. Or MS SQL will give you one answer while Postgres literally gives you a different answer to the same query! And yes, I’m talking about setting the same isolation level in your ORM…

If you use DB2, you’re going to have to use repeatable read isolation to get a consistent result set. Not the case with Oracle.

Database A may give you non-blocking reads; Database B does not. Therefore your app plugged into database A may run just dandy. You switch to B and your users complain of slowness. Maybe you then conclude that Database B is bad, but really the problem is that you perhaps unwittingly designed for Database A, thinking that you were database independent all along.

Many developers (not all) want to treat the database as a black box - they feel they don’t need to know anything about it - they believe they should AVOID knowing anything about it in order to be good object-oriented programmers. The culture supports this notion with terminology such as “data store” and “persistence layer” - just a place to plop down your data and take it out again later, as if it were spreadsheet. In reality, most RDBMSs are very robust with countless features to manage, manipulate, safeguard, and serve your data to many concurrent users.

Why not use the features that make your database special? If you’re using a commerical database, you paid a lot of money for these features. Why use the lowest common denominator? Using Oracle’s analytic queries I’ve seen queries run literally orders of magnitude faster than lowest-common denominator queries/code. I’ve seen an overnight job transformed into a sub-minute job by using Postgres’s native SQL that lets you do, through a single query (not row-by-row): “If a row having the matching ID exists, update, else insert”.

I find it much easier to rewrite the database API (stored procedures) entirely than to deal with the above issues. I’ve used ORM in the form of Hibernate, in order to solve the database independence problem (wasn’t my choice), and I found the cure was worse than the disease - I’d rather translate my 100 PL/SQL functions to PL/PgSQL (maybe a bad example as they’re nearly identical - or maybe a good example as to why it’s not always hard - actually both databases support Java stored procedures so we could even go that route…) than deal with 50 Hibernate XML files. And guess what - even if using Hibernate were easier for me (I realize it’s an opinion), it never solved the aforementioned problems.

So I typically push for database dependence, aka “application independence” or “using the database to its fullest potential”, not database independence. At one job I was told by my supervisor, “all database access must be done through the business objects in order to achieve database independence.” He wasted his time writing his own (buggy) security. Database tables lacked all but the most basic constraints, and contained strings of pipe-delimited name-value pairs rather than using native database types. I asked what we will do if we want to hook up Crystal Reports or MS Access or even (gasp) run an ad hoc query from the command line app? Not allowed, I was told! He wanted to be database independent so much that he sacrificed application independence. He was saving his spreadsheet. Thankfully, most perspectives are not quite as extreme…

Think of how many application technologies/languages/paradigms have come and gone. What do you think is important to companies now: Their 12 year old web apps (remember when “CGI/Perl dynamic web pages” was the hot thing to put on your resume?) vs their RDBMS. How about their 10 year old VB/MS Access/C++ Client/server apps vs. their RDBMS? 20 year old green screen apps vs. their RDBMS? I’ve developed web apps using JSP/Servlets 3 to 5 years ago - I’ve been almost laughed at for not taking advantage of EJBs. The author of this Hibernate book claims most EJB projects failed and ORM is the wave of the future. You gotta keep up with the times - i.e., scrap the all-exalted paradigm of 5 years ago for the new godsend. So… your app vs. your database - which are you more likely to redesign/throw out within 10 years and which are you going reuse/depend on?

As always, just my opinion. I apologize if the above post isn’t the best organized…

you can’t compare apples to oranages!!!

jeff u need a vacation…and yes start using sp.
you shouldn’t be debuging your sps if your design is right. have all your business logic in one tier, data access in another, and data retreval in the sp, then you will have OOP and a very easy to maintain application. and if you like web services, that is just another tier on top of ur business tier, and yes it is platform free if you like that idea.

why would u recompile ur own business tier for a sql change? say you forgot one row, or new rows are added in the tables. come on, i would rather just recreate the package or sp leaving the business components unchanged.

and if ur really gonna distribute ur sql queries to the client side, have fun redistrubuting patchs all day =p

Well I have nothing majorly useful to add but I am a developer, stored procs are a nightmare to maintain and I don’t like burying business logic in them.

ORM for the win.

I am an oracle DBA. from my point of view there were some benefits for stored procs. like bandwidth utilization. but new servers are connected with GB/Sec lan and bandwidth is no more a bottile-neck. Now things are moving to more distributed env. like grid computing. now there is no point in putting bussiness logic and database in one place and say it is better.
In my experience Stored Procs is the major factor in downtime of the database systems and they don’t have measurable benefits.

I do not find writing and debugging stored procedures to be particularly difficult. I am surprised that some accomplished programmers apparently do. While I support the objection to an absolutist policy about using stored procedures, I very strongly favor the use of stored procedures over in-line SQL for the reasons already delineated, but especially the consistent client interface they afford. As for maintenance of in-line SQL in client source code, “Edit-Find And Replace-Find In Files” only works if you actually have access and rights to all of the source files of every application that accesses the database. Personally, I find this whining, harangue against stored procedures to be a type of “Coding Horror” in itself, and from the responses, it appears I am in the majority.

Almost everyone here says either, that using stored procedures is BAD, or, that using stored procedures is GOOD.
After reading the postings, I think, some developers code their complete business logic in stored procedures, while others completely refuse the use of SPs.

I miss a discussion about WHAT should be implemented in SPs and what should not be implemented in SPs. I can benefit from nearly all the advantages of SPs without suffering from their drawbacks, if I do THE RIGHT THINGS in stored procedures.

I think, if SPs are used only to hide the persistence layer’s implementation details from the DB-client (hopefully this client is the business-tier), then they are exactly used for what they are good for.

If they are used for hiding business logic from the business tier, then they are exactly used for what they are NOT good for (Anybody surprised?).

I think, that EVERY layer should hide his implementation details from every other layer, and I am sure, names of tables and fields, DB-structure and so on are implementation details of the persistence layer. Stored procedures are a way (maybe not the only way) to hide these details. In my opinion ‘classical’ use of ORM just hides the implementation of the persistence layer in a dedicated part of the business layer. This is no reason, not to use ORM at all, but in many cases it can be a reason to make well considered use of stored procedures.

As a Business Analyst and amateur coder, I learned a lot from this discussion, but was a bit surprised that no one mentioned transactional integrity (commit/rollback etc).

Sometimes we need for several updates EITHER to work in their entirety, OR for everything to be backed out (e.g. moving money from one account to another). At other times we want several changes to be made visible to other users all at the same instant (as in for example changing a booking from one seat to another in an airline seat booking system).

How would you guys evaluate SP vs inline SQL vs ORM etc. etc. in this area?

An enjoyable discussion, folks.
IMO SP is great for data tasks required by several application modules, and I’m comfortable writing them. Unusual data tasks get buried in C# T-SQL code - which are limited to queries. I use extensive API modules to interface between SQL data and application layer.
It seems some of us are comfortable in writing and using SP, and others in code. Perhaps the best approach is a blend - let each do what it does best in the hands that weild it.

hi there,
what could be an argumentation against following statement (this our current situation):

  • DB enforces useage of stored procs for EVERY DB acces
  • main reason = they must have the possibility to change/tweak/optimize all DB related stuff DURING RUNTIME without the need of a recompilation or other application dependent activity

= this leads to a “abuse” of ORM tools where we try to interop with stored procs within ORM tools like hibernate (which in fact is little bit crazy…)

= but is there any argument against this STRONG requirement from a “almighty” DB section ???

greetings from vienna

The debate have been continued for about 2 years. This is one of the typical software design question that have no absolute yes or no answer. When dealing with this kind of question, developers usually analyze the question in terms of pros and cons, but if we go back and consider the non-function requirement first. It will be easier for us to figure out which solution is better in all situations.
First we consolidate all non-function requirement here, definitely most of them can be achieve with or without using stored procedure, but usually for certain requirement, one solution is better than another. So after we build the full list, we’ll have a rule set that can help us to determine we should or should not use stored procedure in our own scenario.

  1. Performance
    Usually this is the strongest reason for using stored procedure. Without doubt SP can make database operation faster. However it is also a typical remedy to poorly written queries and poorly designed schema. If you believe in the good old 80-20 rule, doing all DB operation in SP is a waste of development effort. With modern profiling tools it is easy to separate slow queries from others. So you just need to tune those queries that really matters. Or wrap them up in SP. On this point, I think a mixed approach is more suitable.

  2. Database Portability
    Stored procedures are never portable, but such portability is not always required. Many enterprise use one single RDBMS product and never change. Usually, under two scenario this requirement is necessary:

    1. You application is a product and your end-users can use it on top of different DBYouYou
    2. plan to use a different DB with your application in the future.
  3. Security
    This is usually necessary if you have more than one applications accessing the same DB, so you may just grant certain application to access SP that it really needs, and hiding the entire schema. However, that means you are using the DB as a point of application integration. It may be the only way before we have other application integration technology like MQ or web service. If you have only one application accessing one DB, and doing the integration outside DB. Why you need SP for security?

  4. Service Interface
    SP can be treat as interface of service for your application, but now web service is a better way to do so.

  5. Unit Testing
    No matter you use SP, or data access layer with plain SQL, you have lots of ways to do unit test. So no difference.

  6. SP as business logic layer
    PL/SQL and T/SQL is not a good language to implement complicated business, better do it in business service layer.

  7. SP as a layer to maintain data integrity
    SP may be a more nature place for maintaining data integrity. Though there is no big different if you do it in the data access layer.

  8. Stop the ripple effect of DB change
    Both SP and data access layer can do the same job.

Finally I want to point out another down side for accessing to DB with SP only. Such practice will easily direct developers to produce a too database centric design. And put too much loading to the DB server. As a matter of fact DB usually is a component that is most difficult to scale up. In contrast, the mid-tier can be scale up easily with modern load balance technology. No matter we use SP or not, we can do some trick like cache to reduce the number of DB access. That can also enhance the performance.

Stored procedures are a travesty to software development everywhere.

All arguments for it ignore the fact that any application with a well thought out architecture will have a data-access layer that will make direct SQL calls from the rest of the application unnescessary.

Why write any code that manipulated data in archaic pseudo-languages of sp? Just write a few libraries of code that will act to abstact the data retrieval and update from the database.

There is really no excuse to burn through development time and resources writing and debugging stored procedures. There is no clear advantage to doing this and in my mind represents a huge flaw in architecture.