21

There is always a debate over the topic - "Whether to put the business logic in Stored Procedure or Not?". If we decide not to use the ORM Tool and not to put the Business Logic in Stored Procedure then where would we put the Business Logic?

In my previous applications I have always preferred putting all of the Business Logic in Stored Procedures only. Then from .NET code I call these Stored Procedures using Data Access Application Blocks. SQLHelper etc. But this can not be the scenario all the time. So I did some googling but ended up in confusion.......

Any suggestionss...?

Improve this question
4
  • I'm biased -> Stored Procs always. But then I'm biased. Forget Agile programming, the sad reality is that in the business world changes always happen ad-hoc and need to be done "immediately". Stored procedures allow that. Its a life saver. Trying to make such changes via the codebase would not be feasible.
    – Darknight
    Oct 10, 2011 at 8:15
  • 4
    @Darknight, Depends heavily on your platform and architecture to make a statement like that. I don't see why deploying a stored procedure to a database is much less time consuming than say, executing a build and deploy script to build a new WAR file, deploy it and restart the app server.
    – maple_shaft ♦
    Oct 10, 2011 at 11:00
  • 7
    Stored procedures - the septic tank of computer science.
    – Mongus Pong
    Oct 10, 2011 at 13:23
  • 1
    Stored procedures - just another tool like any other.
    – sam yi
    Nov 19, 2013 at 16:25

9 Answers 9

Reset to default
16

I would adopt a pragmatic approach - historically the primary 'benefit' of keeping business logic in stored procs is for performance reasons (2.5 tier architecture), whereas separating the business logic into a BLL tier (3/N tier) is generally cleaner from a maintenance perspective, and easier to test (Mock / Stub out the data access).

However, given that LINQ-enabled .NET ORMS such as LINQ2SQL, EF and NHibernate now create parameterised SQL queries, where query plans can be cached, are escaped for SQL Injection etc, I would guess that the move toward 3/N tier architecture is more compelling than ever, and most of the SPROCs (especially query-centric ones) can be avoided altogether. Repository patterns in .NET commonly expose IQueryable / accept Expression tree parameters, allowing for a type safe, yet flexible access to your tables. (Personally in SOA type architectures, I wouldn't expose IQueryable beyond the BLL, i.e. your Service and Presentation tiers should work with a well defined set of methods. Reason is that otherwise you can never fully test your system, and you won't sleep well at night knowing that some arbitrary query issued by a client could hit your DB without hitting indexes etc)

However, in a decent sized system, there will always be a few exceptions, where a really data intensitive piece of code might still need to be written as a Stored Proc for performance reasons. In these instances I would keep the SPROC, and expose the SPROC through the ORM, but still expose the function as a pass-through method on your BLL.

Improve this answer
1
  • 1
    +1 for easier to write unit tests at the application tier, however automated DB unit testing frameworks have come a long way.
    – maple_shaft ♦
    Oct 10, 2011 at 11:03
14

Being a java developer my preference was to put business logic in the BLL (nice and easy source control, familiarity etc etc etc).

However, after working for a large enterprise with many distributed applications using different technologies (C#, Java, Pick (don't ask)) one significant benefit of using stored procedures became apparent :

Stored Procedures can be shared across different applications.

Improve this answer
5
  • Very good point
    – NoChance
    Oct 10, 2011 at 14:26
  • 1
    This is very true, and we use it to good affect in our environment. However, sharing code using the data layer has always seemed a bit dangerous to me. If you have multiple consumers of a given piece of logic / data then I'd rather put a service in front of it versus having multiple consumers of the same database.
    – RationalGeek
    Oct 10, 2011 at 15:34
  • 2
    If you split out your data managment into libraries, those libraries could also be shared across different applications theoretically...
    – glenatron
    Oct 10, 2011 at 15:42
  • 2
    I agree partly. All these technologies are accessing the database directly; so you're using stored procedures to share common code among them. You could just as well solve the same problem by having a middle tier and have the heterogeneous solutions accessing your middle tier instead of your database, and that middle tier shares such code.
    – Liz Av
    Feb 15, 2014 at 16:27
  • 1
    fyi this answer is baloney, 6yrs later - only data goes in the database. You put logic in there you have a a lot of trouble down the line. Just build microservice in your language of choice that accesses the db.
    – NimChimpsky
    Jun 10, 2016 at 19:53
6

Our team have a soft rule here. Sometimes it's better to solve the Business Logic in T-SQL, sometimes it's easier to do it in c# (Business Layer).

So we have a pragmatic solution: Put where it fits better. I know the theory is sometimes very strict about it... but that's theory :-)

Improve this answer
9
  • 2
    Surely this is the worst solution of all? How does a maintaining developer know where the logic is stored? I would imagine that sometimes it also creeps out into the application layer, or even worse, the UI?
    – Paul T Davies
    Oct 10, 2011 at 13:11
  • 2
    Nope. It's always into Business Layer or T-SQL. Figuring out where the logic is stored is probably the smallest problem when it comes to maintenance.
    – gsharp
    Oct 10, 2011 at 13:20
  • What happens when someone joins the team and you tell them this rule? How are they supposed to know where something "fits better"? This almost seems like a non-rule to me. Very subjective based on the individual.
    – RationalGeek
    Oct 10, 2011 at 15:33
  • 3
    Come on guys, serious? We employ peoples that have a brain to think and come with some experience on board. Then.. oh yes they have a mouth to ask and do conversation. I can say that our software needs very low maintenance and new features can implemented fine by almost everyone in our team. can't be that wrong what we do.
    – gsharp
    Oct 10, 2011 at 21:49
  • 4
    I really don't see a sense to misuse c# for things that SQLServer can do so much better, and vice-versa.
    – gsharp
    Oct 11, 2011 at 5:58
3

There are advantages and disadvantages to both (in my opinion):

Stored procedures can become a nightmare if you are not using some sort of SQL source control (which a lot of places don't) and you have multiple developers working on them. Someone can change a stored procedure and forget to update the code that calls that procedure and before you know it you've just built and deployed a site that is going to throw unhandled exceptions (parameter count mismatch etc).

On the other hand, stored procedures allow for quicker bug fixes in certain situations. If there is a bug with a stored procedure you just fix it and you're done. A bug fix in an ORM requires a rebuild. Depending on your build process this could be lengthy / annoying.

Improve this answer
1
  • +1 for the need for source control with stored procs if you are going to use them heavily. Many DBAs that I've worked with are very resistant to this idea.
    – RationalGeek
    Oct 10, 2011 at 15:35
2

We always put our Business Logic in Business Logic Layer. If you put it in Stored Procedure, it will be lost once you change your RDBMS.

Improve this answer
7
  • 16
    The last thing that changes is the RDBMS ;-)
    – gsharp
    Oct 10, 2011 at 7:59
  • So does it mean, that you limit the Stored Procedure to fetching, Updating and Inserting the data....?
    – Pravin Patil
    Oct 10, 2011 at 8:09
  • 1
    Every large system I've seen, the reality is that the database IS the system. Programming languages almost becomes irrelevant at this point as just the "front end"..
    – Darknight
    Oct 10, 2011 at 8:12
  • 2
    @gsharp, that is not always true. You may either want to add another RDBMS like Oracle, or to completely replace the existing one. Or, in some cases, you want to replace real data with dummy data.
    – šljaker
    Oct 10, 2011 at 8:26
  • 2
    @šljaker of course it's not always true. But it's more likely that the program changes (redesign of the software, new programming languages, etc) rather than the DB.
    – gsharp
    Oct 10, 2011 at 10:58
2

"Business logic" is a bit of a vague term. I mean it does not have a single definition. A rule of thumb is to minimize communication between the tiers when you can. So, you don't need to send blank customer name to the server to check it before inserting a row.

There are cases, when a rule is based on a database read. Say you want to transfer money from Account 1 to Account 2. You need to read both accounts, make sure they are in good status and that the amount in Account 1 is sufficient. In this case, the server is a better candidate for this rule because the client (being the BL here) need not issue 3 calls to the database tier for this process.

Of course, if you need your solution to be database independent make stored procs only for CRUD (if at all used).

Improve this answer
1

Logic should be in the BLL always because:

  • It can be tested properly
  • When SQL 20XX becomes obsolete and you need to change to the latest version, you do not have to rewrite your code.
  • People are not tempted to make to make changes on the fly (which seems to be being put forward as a argument FOR SPs)
  • SPs, in my experience, are the single biggest point of developer error, especially after a few generations of maintenance/changes.

I believe there should be a law that states that after an SP is more than X lines long, it does not work as intended.

Improve this answer
2
  • What is wrong with changes on the fly? If there is a bug in a stored procedure and it is easily fixed, then fix it. It's a positive because it means you don't have to do a re-release for something trivial. So long as people don't start masking bugs in code by changing stored procedures then I see no issue.
    – AndrewC
    Oct 10, 2011 at 13:24
  • By changes on the fly I mean things that aren't tested and don't follow a formal release procedure. And yes, sp changes to mask code bugs is something I've seen a fair bit of.
    – Paul T Davies
    Oct 10, 2011 at 21:53
0

We create a services layer that contains all of our business logic implemented in the chosen language and only use the database for queries. This approach is sort of mandated by us because our goal is to create COTS solutions to deliver applications with various database implementations. Hibernate has proven to be a lifesaver for us in these circumstances.

I think the greatest advantage of this approach, aside from database portability, is that you can find all of your answers in one search.

Also, despite some of the answers to a forum, I have a friend working for a fortune 100 insurance company who's done 2 database conversions in three years because the database of choice for the company changed.

Improve this answer
0

In my limited experience, I prefer to maintain data integrity with stored procedures and other database features. For example, if I were implementing a transfer of funds between two accounts, I would write a stored procedure. I find it valuable to be able to use multiple application languages.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.