Stored procedures vs a business logic layer in code

Almost certainly going to get flamed on this one - probably in the same league as 'Emacs or vi?'...

In this podcast, the team that are behind StackOverflow basically say that they think that the days of stored procedures are coming to an end. Now, obviously they're coming at it from a developer perspective, and I'm wondering if there are counter-arguments from a DBA perspective.

more ▼

asked Oct 15, 2009 at 07:29 AM in Default

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

(comments are locked)
10|1200 characters needed characters left

12 answers: sort newest

It breaks down this way, you can build a well-designed front-end application that uses well built parameterized queries and get just about every bit of performance and plan reuse that you can get out of stored procedures. There are two issues that will probably keep stored procedures alive into the future. The first is security. You can do a lot more granular manipulation of security through stored procedures than you can through parameterized queries. That's not needed for lots of applications, but it is for others. The other issue is deployment. I know that some developers and some shops have been able to get deployment down to a fine art so that they can make lots of small code changes and get them into production quickly and easily, so if query tuning is needed in these shops, changes to the code that generates the queries can be quickly rolled out into production. But lots of shops are not able to do this. So having an added layer, through stored procedures, allows for quick deployment and modifications that don't require complete code builds and all the associated testing required.

All this assumes that you're not talking about straight ad hoc SQL. That's still a frightening abomination, performance bottleneck, security hole, and all around problem. I know that lots of developers are in the "deliver the code and leave" mode of consultants. But for those that work full time and have to maintain applications over an extended period of time (I'm working on apps that are over 10 years old and there are 20+ year old apps in the company I work for) making poor design choices, such as ad hoc SQL, have enormous long term repercussions and ultimately have to be replaced with better applications.

more ▼

answered Oct 15, 2009 at 09:17 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

The other assumption is that the developers writing the front end application understand how to code SQL for performance. I've seen too many instances where this was not the case.
Oct 23, 2009 at 09:40 PM Bob Hovious
Excellent point, Bob. I believe in separation of responsibility based on areas of expertise. There are front-end image/style experts, front-end coding experts, business rules experts, database experts, etc. Asking one individual to handle multiple areas often (but not always) leads to less-than-ideal work in one of them. In my opinion, there is no such thing as a "developer expert." There are just too many areas to become an expert at all of them.
Nov 13, 2009 at 02:01 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left
This article talks about why stored procedures are still alive: http://java.dzone.com/articles/2011-great-year-stored
more ▼

answered Jan 25 at 07:21 AM

Venkataraman gravatar image

1k 1 3

That is not a reason why they are alive. It's just an example of how to use stored procedures more effectively in your code. It is very much like Linq in .Net or any fluent/dsl sql abstraction.
Jan 25 at 09:32 AM Grofit
(comments are locked)
10|1200 characters needed characters left

I would say that writing business logic in stored procedures is a bad thing, this is for multiple reasons:

  • You cannot automate tests with stored procedures easily and unit tests are impossible
  • Stored procedures are harder to handle with data migration (roll in/roll out) when using automated build scripts
  • You will have a LARGE scalability bottleneck, these days horizontal scaling is often favored over vertical scaling and it is VERY complex and difficult to scale SqlServer or any other relational database without partitioning data etc. A web service containing business logic can easily be scaled horizontally
  • It obscures intent and is difficult to maintain the logic as SQL is not really a programming language, its a data query language and business logic is a problem to solve, not data to retrieve (although data retrieval is often one of the key parts of business logic)
  • It can often lead to bad practices where SP logic is altered on live servers rather than in a test environment because it is often harder to mock data in the database, whereas in code you can write unit tests to mock elements and constrain a given scenario.
  • It is hard to have a version of a database as the schema may be static but the stored procs may change so without a lot of traceability and discipline the application which consumes the data will never correlate to a given version of the database. This may not seem important however if you find an issue in an application in a given version (lets say 1.2.1) and you need to roll out that specific version with the database at that specific version it will be tough unless both parties have a well defined change and migration plan.

There are lots of points about security, which are valid it is a real thing however most of the time these days databases are hidden away and exposed via web services, if you look at the largest technology based companies they use highly scaleable web services to abstract the data concerns and often use caching or split read/writes to improve performance. In the better architected systems the database is just a small part of a bigger picture, but the moment stored procedures contain business logic they become center stage.

Sure in lots of small companies where they dont have the skill set in place to have unit/integration/acceptance tests, stateless systems, web services etc, however this does not mean they should not be aiming for this, and the moment you put logic in a stored procedure (as a default stance) you are taking steps backwards when you look at the larger picture. A database is there to store data, an application is there to carry out logic, the moment they swap some of their roles then many cries are heard from the future maintainers of such projects.
more ▼

answered Jan 24 at 02:06 PM

Grofit gravatar image


(comments are locked)
10|1200 characters needed characters left
In my experience many large organisation keep the database in service far longer than the application that surround them. I have come across instances where the database has 1980 style thick clients, ASP web clients, Excel clients and others. IT departments are keen to build a brand new .NET front end with an ORM layer abstracting away from the database. The problem is that there is a massive impedance mismatch between the object model and the database, which would result in a very inefficient ORM layer. Database re-design is out of the question because it would require re-write of the many systems dependent on the current structure. In this case stored procedures have proved to be the best abstraction allowing a clean interface to the ORM. Also as legacy applications are retired the database can be transitioned to a more ORM friendly design by changing the interals of the procedure with no impact on the ORM.
more ▼

answered Mar 14, 2013 at 11:16 AM

tanglesoft gravatar image

1.8k 3 4 6

(comments are locked)
10|1200 characters needed characters left

The counter arguement for the DBA is: I will be responsible for writing, testing, debugging, performance enhancing, documenting, coordinate with the daily build and securing the database and the objects I make available to the developers in a timely fashion.

How could they say no?

There are movements in the agile world that want to get away from overly-specified skill-sets. No one is solely responsible for an aspect of the project. Everyone works on everything and even shares their lunch while singing Kumbaya. To be honest, I like the variety as well.
more ▼

answered Jan 11, 2013 at 07:44 PM

Jeff O gravatar image

Jeff O
142 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 15, 2009 at 07:29 AM

Seen: 9594 times

Last Updated: Jan 25 at 09:32 AM