[ACCEPTED]-Arguments for/against Business Logic in stored procedures-business-logic

Against stored procedures: business logic in programming space

I place a high value on the power of expression, and 14 I don't find the SQL space to be all that 13 expressive. Use the best tools you have 12 on hand for the most appropriate tasks. Fiddling 11 with logic and higher order concepts is 10 best done at the highest level. Consequently, storage 9 and mass data manipulation is best done 8 at the server level, probably in stored 7 procedures.

But it depends. If you have multiple 6 applications interacting with one storage 5 mechanism and you want to make sure it maintains 4 its integrity and workflow, then you should 3 offload all of the logic into the database 2 server. Or, be prepared to manage concurrent 1 development in multiple applications.

I am thoroughly against it. One of the 17 biggest reasons is the first reason earino 16 stated - it lives in one place. You can 15 not integrate it into source control very 14 easily. It is next to impossible to have 13 two devs working on a stored proc at the 12 same time.

My other main complaint is that 11 SQL is just not very good at representing 10 complex logic. You have no concept of scope, code 9 tends to be copy-pasted because there is 8 a less ability to reuse code (as opposed 7 to an OO language).

You have to give developers 6 access to the database to develop there. In 5 many organizations I have worked at the 4 data people are in a different world than 3 the devs, with different permissions, etc. Keeping 2 the devs out of the database in these cases 1 would be harder.

I'm of the school of thought that says that 9 as long as business logic:

• lives in one place
• where it is properly documented
• proper access is provided through services that can be loosely coupled
• through a published abstracted interface

I don't care if 8 the logic lives in a stored procedure, in 7 a J2EE middle tier, in a clips expert system, or 6 wherever. No matter where you store our 5 business logic the "law of conservation 4 of misery" is going to guarantee that someone 3 will say it was the wrong idea because component/repository 2 X needs to be swapped out for technology/method 1 Y.

Some thoughts: Please note this is a Java 58 centric response, but its the bulk of my 57 recent (last 10years) experience

(1) Concurrent 56 development by a (a large) team of developers. If 55 you're application is sufficiently complex 54 that each developer can't set up their own 53 private version of the DB (with attended 52 links/ref data/etc...)it is very difficult 51 to have an entire TEAM of developers all 50 working on the same set of PL-SQL (for example) packages 49 at the same time stored in a shared DEVL 48 DB? Then your stuck (my experience) with 47 working in a DB with invalid procedures 46 / mismatch of code to tables as people make 45 changes...

As a Java architect, I think its 44 much easier to have each developer have 43 a private JBoss instance on their desktop 42 and work easily on their own set of functionality, and 41 integrating at their own pace without impacting 40 everyone else ... which brings me to ...

(2) Continuous 39 Integration toolsets While there exist some 38 similar 'concepts' in the DB world, my experience 37 has shown me that the combo of (i'm picking 36 my current best-of-breed favs here):

• mvn - build system
• junit - automated unit testing
• nexus - repo manager (manages artifact's lifecycles version, snapshots and releases)
• hudson - ci build server
• sonar - static analysis tool / code coverage reports / ALOT more

Running 35 a large project using all of the above (free 34 tools) allows a consistent / easy way to 33 deliver XP to the masses and enforce quality 32 controls over a whole IT staff. Oracle / PL-SQL 31 doesn't have the toolsets to match

(3) tools 30 / libraries / etc... Java has access to 29 an amazing set of services that other platforms 28 cannot touch - some free, some not. even 27 basic ones, like log4j (yes they have it 26 for PL/SQL, but pulease...its not nearly 25 the same) allows for things like allowing 24 developers to create flexibly adjustable 23 logging that can be changed on the fly (perfect 22 for dubugging). Automated API documentation 21 (via javadoc). Automated unit test coverage 20 reports. Incredible IDEs (Eclipse) with 19 integrated debuggers / autodeploy to app 18 servers. An API to interface with every 17 type of service under the sun, open source 16 libraries to do ANYTHING, and 100% support 15 by every vendor

(4) reuse of services. what 14 someone commented on is true. If you have 13 heavy duty data driven business rules then you can argue that these 12 should live in the DB layer. Why? to prevent 11 having the middle tier(s) all having to 10 duplicate that logic.

But the same can be 9 said for business rules that are not data driven or sufficiently complex that OO is a more natural choice. If you 8 stick ALL business logic in the DB, then 7 they're available only via the DB.

• What if you want to have validation done in the client or middle app tier and save a round trip to the DB?
• What if you want to cache read only data in the middle tier (for performance) and have business rules execute against the cached data?
• What if you have a middle tier service that doesn't require DB access, or you have a client that can supply their own data?
• What if the data dependent portion of the business rules then needs to access external services? Then you end with with fragmented business logic that looks like this:

i

retCode = validateSomeDate(date);
if (retCode == 1) then
   evaluateIfCustomerGetsEmail(...)//probably more stored proc invocations here...
   sendEmailMsg(....)
else if (retCode == 2) then
   performOtherBizLogicStuf(...) //again, may need data, may not need data
   triggerExternalsystemToDoSomething(...) //may not be accessible via PL/SQL 
fi

I'm sure 6 we've all seen systems written like the 5 one above and had to debug them at 2AM. Its 4 extremely difficult to get a coherent sense 3 of a complex process when the business logic 2 is fragmented between tiers, and in some 1 cases it gets to be impossible to maintain.

"You can not integrate it into source control 22 very easily." - if you put the code that 21 creates the stored proc into a script that's 20 version controlled, that objection goes 19 away. If you follow Scott Ambler's agile 18 database ideas, that's exactly what you 17 should be doing.

Not all developers are good 16 data modelers. I can think of horrible 15 schemas created by developers who thought 14 that a dabbling knowledge of SQL made them 13 database experts. I think there's a lot 12 of value to having developers working with 11 DBAs and data modelers.

If only one application 10 uses the database, I'd say that business 9 logic can appear in the middle tier. If 8 many apps share the database, perhaps it's 7 better to put it in the database.

SOA offers 6 a middle way: services own their data. Only 5 the service has access to the data; getting 4 to data means going through the service. In 3 that case, it's possible to put the rules 2 in either place.

Applications come and go, but 1 data remains.

One more reason NOT to store business logic 5 in sprocs - limited scaling abilities of 4 the DB. It is very common situation where 3 your database is your bottleneck, that is 2 why it is a good idea to take as much load 1 of the DB as possible.

Business logic should be encapsulated in 30 one place. We can guarantee that the logic 29 is always run and run consistently. Using 28 classes that all activity involving an entity 27 on the database must run through we can 26 guarantee that all validation is run properly. There 25 is one place for this code and any developer 24 on the project can easily open this class 23 and see the logic (because documentation 22 can and does get out of date, the code is 21 the only reliable form of documentation).

This 20 is difficult to do with stored procedures. You 19 may have more than one sproc dealing with 18 the same table(s). Chaining multiple sprocs 17 together so that the logic resides in only 16 one gets unwieldy. That is strike one. How 15 do you determine "What are all of the business 14 rules surrounding entity X" within the database? Have 13 fun searching thousands of sprocs trying 12 to track that down.

Number two is that you 11 are tying your business logic to your persistence 10 mechanism. You may not store all of your 9 data in the same database, or some may reside 8 in XML etc. This type of inconsistency is 7 difficult on the developer.

Validation is 6 difficult to perform if the logic resides 5 only in the database. Do you really call 4 a sproc to validate every field on your 3 data entry form? Validation rules and business 2 logic are close cousins. This logic should 1 all be performed in the same place!

My few observations:

In favour of stored 26 procedures:

• after some time of project life, in 25 most cases the bottleneck is the database 24 not the web server - and stored procedures 23 are much, much faster

• using sql profiler 22 with ORM generated sql queries is very difficult; this 21 is easy with stored procedures

• you can deploy 20 a fix for stored procedure instantly, without 19 a service window

• for performance, it is easier 18 to optimize a stored procedure than ORM-code

• you 17 may have many applications using the same 16 db / stored procs

• any complex data scenario 15 is a vote for stored procedures

In favour 14 of application/ORM:

• you may use code repository 13 (with stored procs it is still possible 12 but expensive)

• java / c# language is a better 11 tool to express business logic

• java / c# is 10 easier to debug (except for the dynamically-generated 9 ORM sql)

• independence of database engine 8 (however this is not very likely that a 7 project will change database to another 6 one)

• ORM provides data model which is easy 5 to use

In my opinion: for large projects 4 - go for stored procedures, for the others 3 - Application/ORM will work fine.

In the 2 end of a day, the only thing which matters 1 is the database.

+: SQL server sometimes optimizes the code

+: You 9 are forced to pass parameters, which limits 8 SQL injection issues

-: Your code depends 7 on a single database (some dbs don't even 6 have SP)

-: To change code you need to connect 5 to database

-: Logic is not organized well

Personally 4 I'm against it, but I had to use it once 3 on a really busy website. Using SP in MS 2 SQL brought huge benefits, but once I implemented 1 caching those benefits were not so big anymore.

There is a saying...

When all you have is 20 a hammer, everything looks like a nail.

In 19 my humble opinion there is no one answer 18 that will fit all circumstances. It seems 17 to me that many people just assume that 16 putting Business Logic in the database is 15 always wrong.

A lot of work has been done 14 to make transaction processing, especially 13 bulk operations, very efficient when done 12 on the database side. Also the code management 11 in databases has vastly improved since most 10 of the opinions against databases were formed.

I 9 think it is wrong to consider the database 8 server as just a persistence layer. If 7 your processing activities are most efficient 6 when done on the DB Server then do them 5 there.

If not then do them elsewhere.

It 4 all comes down to what best fits the application 3 you are working on at the moment, the team 2 with whom you are working and the customer 1 who hired you.

That just my 2 cents.

You can unit test business logic when its 5 in a business logic layer. If it's completely 4 separate the persistance operations can 3 be mocked so you are testing the BL only. A 2 stored proc is far more difficult to maintain/debug/unit 1 test than e.g. linq and c#.

DBMS != Application server

• Functional programming (DB Stored procedure) vs OOP. For big programs is OOP just standard.
• IDE - eclipse, intellij, netbeans with all plugins for debugging, testing and analysis works only with real programming languages. Static code tools.
• Version control if you get one for PLSQL & co. is great. If you get "synchronize view" direct from you IDE - you are real the lucky one.
• Scale your system. For DB system is hell. You need expensive hardware for other "nodes". Replication. And probably licence for every node. Don't forget you are still in "functional programming" and effort to understand and maintain such systems is much bigger.
• You are stuck with your DB, try to change or add a new one from another company
• And so on...

Stored procedure 2 for business logic is bad practise today. Use 1 3-Tier architecture instead.

There are different kinds of "business logic". Consider 16 partitioning it based on how it's related 15 to other layers or services. Here are some 14 rules of thumb from an MVC perspective:

a) In 13 the database (stored procedure) if it's 12 mostly data-related, and can be done with 11 joins and relatively simple WHERE and SELECT 10 clauses.

b) In the controller if it's mostly 9 routing or dispatching related; that is, larger-scale 8 UI flow control in terms of screen or resource 7 selection.

c) In the model or view-model if 6 it involves complex or intricate computations 5 and/or conditionals.

d) In the view (such as 4 Razor) if it's primarily a display issue, such 3 as "friendly" re-formatting and relatively 2 simple to implement. (If it's complex, consider 1 putting it in a view-model.)

My rule of thumb (once I recognized what 55 it was by thinking about the question) is 54 that stored procedures should contain code 53 that ensures data integrity within the database, whether 52 the stored procedure prohibits certain data 51 insertion, deletion or modification, or 50 makes other changes necessary for data consistency. Business 49 logic, especially logic that cannot be implemented 48 in a handful of set-based operations, should 47 be implemented elsewhere. Databases are 46 not applications. Databases should be the 45 ultimate authority for relationships and 44 constraints, even if the business rules 43 are also implemented elsewhere for, say, the 42 provision of feedback in user interface 41 code that reduces postbacks from a web server 40 or eliminates otherwise unnecessary hits 39 on a busy server. One can argue whether 38 "data consistency" includes the result of 37 complex processing of complex business rules, but 36 I think it's usually clear once the context 35 is understood. Not all business rules are 34 implemented as data relationships or constraints. Not 33 all operations in a stored procedure are 32 faster than code running in a separate process, even 31 on a process running on a separate machine 30 across a network. I recently did a demonstration 29 showing that many operations in SSIS, for 28 example, (INSERT INTO () SELECT FROM) perform 27 faster in SSIS running across a network 26 on a separate machine than running in a 25 stored procedure (that also inserts the 24 results to a database across the network). This 23 is an almost unbelievable result (where 22 SSIS is faster than raw SQL statements), and 21 demonstrates that the discovery of the best 20 optimization of any performance issues comes 19 from reality (testing) and not from logic 18 based on only a few concepts. (We still 17 have to make decisions on what to test by 16 rules of thumb learned by experience.) (SSIS 15 performed faster by automatically implementing 14 multithreading and pipelines, using BULK 13 INSERT even where it wasn't specified in 12 the raw SQL statement, and sending batches 11 of inserts on one thread while creating 10 additional BULK INSERTs on other threads. In 9 this instance, it performed about twice 8 as fast as raw SQL statements.) When I used 7 to teach programming and SQL Server courses, PowerBuilder 6 users seemed to have the statement "Native 5 drivers provide the fastest data access" burned 4 into their tongue, and while it might be 3 justified through additional (unrecognized 2 by them) explanation, the thinking behind 1 it is misleading.

@Nick "I am thoroughly against it. One of 39 the biggest reasons is the first reason 38 earino stated - it lives in one place. You 37 can not integrate it into source control 36 very easily. It is next to impossible to 35 have two devs working on a stored procedure 34 at the same time."

Not that I'm arguing for 33 putting business logic on stored procedures 32 (all the contrary). But these reasons you 31 put forward make no sense. A stored procedure 30 is simply a sql/DDL artifact that can be 29 stored on source control, and which is also 28 a deployment artifact (that is, something 27 handed over to the dba for deployment, much 26 the same way you would hand over your war/ear 25 artifacts to the IT/deployment liasons) One 24 or more developers can work on the same 23 stored procedure off source control just 22 in the same way you'll do with plain old 21 source code - by branching, versioning and 20 merging.

Now, if the only copy of the stored 19 procedure (and the packages that contain 18 them) only exist in the database, then obviously 17 you cannot control that with source control 16 (and all the problems associated to that). However, that's 15 not a problem of stored procedure but a 14 problem of ineptitude in regard of how to 13 use that code. It is as equally a display 12 of ineptitude as having only one copy of 11 your source code living on production.

I've 10 worked in systems with massive amounts of 9 code, both Java and PLSQL/DDLs and they 8 were all versioned on clearcase. They were 7 all treated as source code that would be 6 compiled and deployed with a strict process, with 5 different teams working on them. Never had 4 any problem as what you are describing.

There 3 are context-specific reasons not to put business logic in 2 stored procedures, but these aren't valid 1 ones.

Performance will be massively improved by 23 moving logic into the stored procs, especially 22 if explicit transactions are involved.

In 21 my experience, application developers aren't 20 very good at writing optimised database 19 code, and don't tend to think about concurrency 18 or performance issues.
If business logic 17 is kept in the application layer, you tend 16 to have to shift large amounts of data (often 15 in many round-trips) across the network, duplicate 14 it in memory on the DB server, and at least 13 once in the app server, and do a load of 12 row-by-row processing in the app while you're 11 holding open a transaction. Then the app 10 developers complain that the database is 9 slow and keeps deadlocking.

If you put the 8 logic in the DB wherever it's possible, you 7 tend to just pass a few parameters across 6 the network, transactions aren't held while 5 you wait for network resources, and the 4 whole thing goes like greased lightning. Databases 3 should of course go in source control like 2 any other source code.. there are plenty 1 of tools for that.

With all this micro-services and micro business 16 components ideology we are way ahead of 15 questioning the right place to put our business 14 logic.

Even though the ideology is well accepted 13 we still have temptation and some instances 12 where we ended up putting some decision 11 making and business logic in database. It’s 10 worth visiting detailed answer to why it 9 should not be done, but at macro level I 8 would advise thinking about one good reason 7 why it should stay in stored-procedure and 6 not in the application layer.

Having that 5 counter thought process would always direct 4 taking the right decision. Ask these questions 3 before deciding:

1. What if down the line we change our database from SQL to Mongo?
2. What if want to expose API which takes the data (that database is providing) and apply this business logic on top?
3. Unit testing this business logic?
4. SDLC steps involved if we make change in conditions of this business logic?
5. What if we need another user input for the decision making in this business logic?
6. What if it requires high computation power (not data processing) and we want to be run off a separate process (or platform) ?

In all cases it would make 2 natural choice to not put any logic (other 1 than just data retrieval) in business logic.