Business logic doesn't go into the database
If we're talking about multi-tier applications, it seems pretty clear that business logic, the kind of intelligence that runs a particular enterprise, belongs in the Business Logic Layer, not in the Data Access Layer.
Databases do a few things really well:
- They store and retrieve data
- They establish and enforce relationships between different data entities
- They provide the means to query the data for answers
- They provide performance optimizations.
- They provide access control
Now, of course, you can codify all sorts of things in a database that pertain to your business concerns, things like tax rates, discounts, operation codes, categories and so forth. But the business action that is taken on that data is not generally coded into the database, for all sorts of reasons already mentioned by others, although an action can be chosen in the database and executed elsewhere.
And of course, there may be things that are performed in a database for performance and other reasons:
- Closing out an accounting period
- Number crunching
- Nightly batch processes
Naturally, nothing is engraved in stone. Stored Procedures are suitable for a wide array of tasks simply because they live on the database server and have certain strengths and advantages.
Stored Procedures Everywhere
There's a certain allure to coding all of your data storage, management and retrieval tasks in stored procedures, and simply consuming the resulting data services. You certainly would benefit from the maximum possible performance and security optimizations that the database server could provide, and that's no small thing.
But what do you risk?
- Vendor lock-in
- The need for developers with special skill sets
- Spartan programming tools, overall
- Extremely tight software coupling
- No separation of concerns
And of course, if you need a web service (which is probably where this is all heading, anyway), you're still going to have to build that.
So what is typical practice?
I would say that a typical, modern approach is to use an Object-Relational Mapper (such as Entity Framework) to create classes that model your tables. You can then speak to your database through a repository that returns collections of objects, a situation that is very familiar to any competent software developer. The ORM dynamically generates SQL corresponding to your data model and the information requested, which the database server then processes to return query results.
How well does this work? Very well, and much more rapidly than writing stored procedures and views. This generally covers about 80% of your data access requirements, mostly CRUD. What covers the other 20%? You guessed it: stored procedures, which all of the major ORMs support directly.
Can you write a code generator that does the same thing as an ORM, but with stored procedures? Sure you can. But ORMs are generally vendor-independent, well-understood by everyone, and better supported.