Why and How to use stored procedures -- even you do not like them

Why and How to use stored procedures -- even you do not like them

1. Stored procedures certainly have their values. For example, when you do not use web services, and you have multiple-platforms, stored procedures provide all the benefits of web services. As a matter of fact, web services (or application servers) are simply stored-procedures-moved-out-from-databases.


2. However, in everyday development, it is antique practice to do stored procedures. I know, M$ marketing and some tutorial demo people are still recommending it; but if you take the words from marketing people or tutorial people as your engineering guidelines, then, you are kidding yourself!

3. The real valid reason of using stored procedures is consistency. If you are in a M$ shop, and if there are a lot of code using SP, and people are used to using SP, then, you have to use it -- until there is a big project that you can break the obsolete tradition (I will explain when next).

4. The above reason also implies that SP does not really hurt that much, it is just a little bit waste. As a result, you should not be too enthusiastic about removing SP. You must choose your battle, concentrate on making productive changes that really benefit the team, not making those purposeless changes.

5. Then, when should you push really hard to eliminate the SP practice? When you use OR mapping! Until then, keep using SP -- it is harmless for now.

6. Of course, you should not put logic in SP; or, relaxing it a little bit, you can put "data logic" in SP -- frankly, it is really a political compromise; for my taste, absolutely no logic at all -- the whole SP will go away, why bother to think about where to draw the line between "data logic" and "business logic", crazy. If you think about it, "data logic" is really just a performance optimization concept. As a result, using it as everyday practice guideline is really counterproductive.