a companion discussion area for blog.codinghorror.com

Who Needs Stored Procedures, Anyways?


LINQ o Hibernate for the best.
No SP calls
No sql embedded.

Dynamic SQL code generated for you on the fly by your HighLevel Programming language.
Use it on top of well designed Views.
SP for the Update/Delete/Insert statements, either called manually or integrated in the LINQ or Hibernate Paradigm (which eventually supports SP calls for such statements)


I worked at a Pfizer Central Research in 1998 when
VIA GRA (the space was needed to post…sheesh!) was ratified and I was the WORKFLOW ADMIN and in charge of supporting the Medical Document Imaging System for the Case Report Form Library and I did everything in SQL on INFORMIX using DB-ACCESS.

Just be sure that ALL drives are MIRRORED on PRODUCTION DATABASES, and the servers are powered by a tested UPS.


Pingback from StackOverflow: http://stackoverflow.com/questions/216569/are-the-days-of-the-stored-procedure-numbered


I couldn’t disagree more (at least for SQL Server)

I have seen massive performance differences. Procedures can be cached (heck, there is even a dedicated procedure cache).

There are huge performance hits. Parsing and execution plants are just one hit. I run about 500 concurrent users and even a .5 second speed improvement for a common process can lead to hours of saved CPU time per day.

Also - I find maintenance easier and it DOES integrate with VS (it even did back in 2004) so I can’t see an advantage in NOT using procedures.

No - hold that thought. If you are writting a small one-off application that is never actually going to be run live, then sure. Otherwise, I simply wouldn’t use embeded code (not evel LinQ - read up the performance impact of that over-hyped technology).


Philip, I believe when you use sp_ExecuteSql for dynamic sql it also creates and caches an execution plan. There’s a good chapter in this book (http://www.microsoft.com/MSPress/books/8564.aspx) about dynamic sql and how in some cases it’s faster than stored procedures.


It seems like that the writer has learned a new technique Web Service and just as a newbie trying to use it anywhere he can he is refusing to accept the stored procedure and is blindly advocating the use of Web Services. I behaved same when I was 18. Jeff forgets 1 thing…not all client apps developed by many different developers may utilize a web service. Not everyone is doing development in VS.NET


Someone should take a look at the Code Image… Only a fool can prefer inline code for this.




I think some of you are picking on SPs because someone shoved business logic in there. Business logic is supposed to be its own layer folks.

Declaring using SPs a dubious practice because people aren’t using them correctly is like saying guns are bad because they shoot people. Just put the weapon down and back away slowly if you don’t know how to use it.


Wow, long read. I support the moderate voices here. (And the blog author’s quixotic spirit at the time). I love SQL and MS SQL Server. And yet when permitted… I’ve had great success with dynamically compiled sql. I put it in a DAL so it’s easy to find. I generate the sql for most of my procs/udf with C# so they’re easy to maintain. (Not so easy to pitch the homegrown solution now that we have Linq). I have no philosophical issues that require my DAL to be dumb about persistence, and since my clients aren’t made of money, I don’t think I could advise my client that it’s a must…

I always use a proc when a lot of data needs to flow across several steps - situations where you might use a temporary table-type variable for large intermediate results and in the end return a relatively small amount of data to the middle tier.

Way back when, mandatory crud procs was the common wisdom. For all the reasons mentioned by the free thinkers, it’s not so dogmatic anymore but it’s still hard to argue against a zealot with a list of undated material to back up their rhetoric, superstition and ego.

I have seen abuse of inline SQL. Especially with recursive calls… Even recently, a large corporation had amazing engineering in many areas but their DAL consisted of a couple of methods that took a concatenated string of sql and returned an untyped dataset, (with no compile). They didn’t care. That was most likely politics, but reading through the comments, I guess smartly normalized tables went out of fashion for awhile?

At any rate, it’s nice to see MS remove the web service from SQL Server. It’s productive to have a full featured db on tap. Oslo is making no apologies.


I don’t understand the whole make it a web service deal. The examples above all talk about VB with dynamic SQL versus VB with SPs. If I’m running a VB app then I already have a connection to the database. I am presumably inside a transaction. Now instead of reusing an existing resource to access the database, you want me to open a socket and call a web service? There are several issues here:

  1. That web service call is outside my transaction so I can’t roll it back should something downstream require a rollback. REST and transactions don’t really mix.
  2. That web service needs to open a database connection and rerun all the authentication code I’ve already run when the client logged in. If there are several calls to the abstraction layer, there are several database connections.
  3. My deployment is now more complex. Before I had a client application and a database server. Now I’ve added an http server to the requirements.

Jeff’s argument that SPs are not the only way to section off the database through APIs. That’s true. MQ services and CORBA calls are also ways to do this. They benefit though from maintaining transactional integrity through the use of transaction managers. REST-based web services are the antithesis of transactional programming.


Another point that I should have made above - if you use stored procedures, users can run them directly from the database in situations where you just want the raw data. This means that your users do not require sql skills, and nor do they table-level permissions - they simply need permissions to the procedure.


learn to use database! please!


I have worked on projects architected from both perspectives, and have come to the conclusion that:

  1. Using SPs for CRUD is insane. If you do this, most schema changes require changing the SP interfaces…when this happens developers will choose not to change the schema because it is too much trouble. Instead in many cases they will choose to overload fields, make use of Table1.ExtraColumn1, Table1.ExtraColumn2, etc…refactoring a schema in any useful way is a huge PITA. Making refactoring harder makes refactoring not happen.

  2. Using a normal language like Java or C# to do reporting is usually way too much coding and the result is extremely slow reports. This is one case where business logic will usually end up getting duplicated in the middle tier and data tier as reports will need to do calculations that the middle tier also has to do (like sales tax calculations for example).

  3. The security argument in favor of using SPs for everything doesn’t hold water. Any security setup that you have can be implemented with a proper application of table-based security with roles and views. This is only even necessary if you have to have rock-solid database security. 90% of the time you can get by with security implemented in the middle tier. If you are paranoid about this, just stick your middle tier behind a trusted authentication wall (a physical application server) which has access to the database.

  4. Business logic is much easier to implement in a real OO language than in T-Sql, Pl-Sql, or whatever.

  5. With a modern ORM, you can detect schema changes and get compile-time errors when a field is renamed, a table is renamed, field made not-nullable, etc…try doing this with SPs that can exist not only on one server, but may be spread all over the place using linked server connections. Bottom line: if you rely on SPs for business logic, changes are HARD and very error-prone.

  6. Version control…TFS has some decent database version control capabilities, but TFS is very heavyweight and expensive. In general, keeping your code in actual code files saves a lot of time when you need to roll back a change.

  7. Unit testing is much easier when your business logic is contained in an actual class where dependencies can be mocked. You cannot do this with SPs…the best you could do is some awkward test database configuration which only one developer can use at a time if this database is on a centralized server.

  8. If you use SPs for your business logic, all the dependencies for this business logic had better exist in the database! Need to check the results of a web service to validate some input? Have fun with that using T-Sql. Want to validate input without putting it in the database first? Try doing this in an SP for the rule: An new order must have at least one OrderItem.

Like I said, I have seen both sides of this and can tell you with certainty that a standard business application is much more maintainable and flexible with business logic in a real middle tier. I have seen no good use for SPs with the exception of reporting and mass, complicated data manipulations.



code example for OOP vs SQL:


Declaring using SPs a dubious practice because people aren’t using them correctly is like saying guns are bad because they shoot people. Just put the weapon down and back away slowly if you don’t know how to use it.


Database engines are designed to be able to process sets of data quickly and efficiently and compile Stored Procedures for this very purpose. MS best practices also recommend the use of Procs wherever possible for reasons of performance, security and data abstraction. I work as a DBA in a development environment and every sane developer I have met firmly agrees that SPs are the way to go. Like everything in IT there is more than 1 way to achieve the same end result but some ways are most definitely better than others.


This is like debating the value of mineral deposits to a tree. The true reality is that for the majority of development environments it would be fine to put that tiny 500 row CMS database in a csv and access it with a text driver. Lots of OO developers contributing to this have never…

  1. Tuned a highly concurrent RDBMS.
  2. Agonized over multi-path statistics optimizations on a VLDB.
  3. Understand what index optimization is.

And even if you do know how to write explicitly parametrized dynamic calls in the middle tier and want to show off your new-found skills, what about your colleges? What about that new junior developer that I have to deal with on a daily basis? What about the normal lazy sloth that just wants to spit out code as fast as possible so they don’t miss Grey’s Anatomy? Son, we live in a world that has walls, and those walls have to be guarded by men with guns. Whose gonna do it? You?




So will Yukon help with any of these problems?