Using Stored Procedures as the Business Logic Layer

Question

The company I'm working for is currently using Stored Procedures (in the MsSQL server backend) as their Business Logic Layer. The actual Business Logic DLL just calls the sProcs and basically manages the UI (events, data binding, etc.)

I think there's something wrong in the setup, although I'm not sure how to explain it to my colleagues. Btw, the system works.

Is the "Best Practices" in my workplace wrong? Or am I just overthinking this?

Answer 1

We do that.

That's because we support scenarios where users connect to the db using programs other than the intended software (like, SQL Management studio, osql and Excel).

When you directly connect to a database which is no more than data storage, you can mess everything up as there's no rules that would stop you. These rules only exist inside the program that uses this database, and if you don't use that program, you can use you I-can-write-to-this-table permissions to do stupid (or fun) things.

When you only have permissions to execute stored procedures, you can't.
I personally think it's a better approach.

Answer 2

GaiusSensei - it's perfectly fine to work in that way as long as the business domain is able to handle seismic shifts in business practices. i think the debate is still rife between SP's and BLL dll's and no doubt, there will be plenty on both sides in this thread. However, from my own experience over a range of projects in the past 10 years, here are my observations supporting the BLL dll approach:

• logic contained in the BLL can be 'agnostic' of the storage medium and therefore more flexible to change (tho how often this happens is debatable)
• Finer grained control over business permissions ACROSS a range of applications that rely on the datastore. By this I mean the core tables whose integrity must be maintained at a level specific to it's use within the business application in question.
• BLL logic can be encapsulated in self contained classes that can be re-used in other areas of the business and or project. The class can even be written as a sealed class or extensible depending on your target 'audience'
• Unit testing - this (in my experience) is a black art if used inside SP's. under java/c# etc, this is an standard and some would say mandatory practice now.
• Maintainability. By keeping well organised interfaces within a BLL dll scenario, you make it easy for supporting developers to extend your classes without breaking existing logic
• Portability. your BLL (depending on the language implementation) can be hosted on a variety of platforms. Likewise, the injection of the implemetation of the datastore can literally be anything from an xml file to mysql, mssql postgres etc, etc.
• Standardisation. The data architect can define exactly how each data element should be taken from the database and how each item should be saved (tho this would be better located in a DAL dll). Thus, the cost of entry for new developers as well as seasoned, on the project is much reduced.

The list goes on but, these are my top of the head PROS for adopting a BLL approach.

Looking fwd to the many spins on this one :)

jim

[edit] - i'd also add that this BLL should NOT emit any UI information either, other than (as you mention) to convey events etc. each UI layer (relevant to the target device -browser/mobile device/factory) should reference the BLL and do it's own 'thang' with the data. I'd further add that below the BLL would be your DAL layer. this layer could be considered a 1-1 reference with the underlying datastore.

Answer 3

I would say that stored procedures do not lend themselves to unit testing and refactoring nearly as well as business layer logic in say .net/java. I would keep logic out of the DB as much as possible, the main exception being inherently set based operations where a DBMS would excel.

Answer 4

(I added the "subjective" tag)

I prefer to use stored procedures, except in small apps that I whip out because messing around with stored procedures takes a little extra time.

Christopherous 5000: you can still do unit testing with stored procedures

I tend to agree with the answer of these two similar questions:

• Business Logic: Database or Application Layer

• Should the data access layer contain business logic?

Answer 5

It sounds like your Business Layer is actually the Data Layer and your application does not have a Business Layer, but I digress...

Best Practices are overrated and change with time. If what they are doing works and they are content with it, then there is not much that can be done.

I can't tell you how many projects I've been on where the new guy comes on board and thinks the current architecture needs to be changed. I've done it a few times myself. It's not a good place to be. The status quo will fight you to the end. If you are really set on changing the current system, build some creditability. In 3 to 6 months start suggesting better ways to approach some of the existing infrastructure. If you really don't like the current architecture then leave the company.

The application was written with the best intentions. The original developer(s) was constrained with time, experience and technology.

Applications like the one you described are ripe learning opportunities. Note the failing points, learn from it's successes. You'll be amazed what you learn.

Answer 6

I think stored procedures are fine as part of your business logic. I don't think you need to have your whole business logic in one dll. However if you have a business layer that is managing the UI I think that you need to separate the UI management from the business layer with some sort of framework. Separation should be functional, don't embed data in you stored procedures or you business logic and vice versa. I also think that if you have other programs, such as excel or reporting, accessing the data that they should be going in through a discrete data source, web service or whatever.

I use to work on client server systems with mainframes where you get real separation of three layers and real bone-headed inflexibility. Modern applications need to be a little more open in implementation and have business rules, particularly for validation, at all layers. That doesn't mean that your design model can't have separation, it's just that business rules like 'is blank or day of week' have to be implemented across the UI, business layer and the data.

If what you've got works, just figure how to work with it going forward.

Answer 7

It depends.

It depends on what you are calling business logic.

Certain things need to be enforce in the database - particularly anything where some other process is going to have assumptions about the nature of what the database presents.

If all users of the database assume that when the last offspring of a billing party is deactivated, the billing party status needs to be altered, then this needs to be enforced in the DB - either in a SP which is the only way to perform the operation or a trigger, or a constraint or something. This is the kind of thing - low-level business logic which is really critical data integrity logic at the business level - which is OK to have in the database.

High-level business logic does not fit in the database - for instance when a patient cancels their last appointment and need to go on a recall list - that is not a data integrity issue - all callers of the system do not assume that patients without appointments must be on the recall list.

The distinction is subtle and it's why people have difficulties with "business" logic in the database. I recommend only things which it is assumed that the database protects and presents at the database perimeter to all users of the database be implemented in the database - this business logic is below the DAL layer and is part of the fundamental database design. I still advocate in traditional architectures a business-blind DAL above that and a real BLL above that.

Having said that, it is certainly possible when the DAL is really trivial SP pumping to have your higher level business logic in the database as well, and when you do, it is not as clear which things are low-level and which are high-level (unless you have two databases, one built on top of the other - which is not necessarily a terrible idea). You've effectively written a part of your business logic in SQL instead of a traditional client app.

That doesn't necessarily mean your layers are too tightly coupled or you've got a bad architecture - just like any system, the language choice for different layers doesn't necessarily point to an architectural issue. Only by looking at the layers can you tell if you have an architectural issue.