Putting Logic in stored procedure

[upstream]

hi everyone,

i was developing a windows app. and im putting business logic in the stored procedures if applicable, e.g instead of getting data in the database and process it in the application, i just get the data that the stored procedure has already processed.

question is, is this a good practice? or it is still better to process data within the application level?

[szlamany]

I have had 10 years of really great success having nearly 100% of the business logic in my stored procedures.

Releasing an enhancement that only requires a SPROC change is truly a good time!

[techgnome]

It's a mixed bag... I've done a lot of BL in the SPs, and so has slamany... and it works well for what it is... but it works best when it's set-based... loops and large data processing on individual rows can lead to problems. I tend to put it where it makes sense... sometimes I find it easier to put data together in SQL sometimes... like aggregates... so, eh.

-tg

[ntg]

I've found that it's good practice to either put as much logic in your stored procedures as possible or none at all. Anything in between results in a mixed design which is confusing and difficult to maintain.

[jmcilhinney]

There are different views on this, and often the individual's opinion depends on their background. Someone who comes from a DB background will generally want as much logic in the database as possible, while those coming from a high-level language background will want the opposite. Certainly, databases are optimised for performing certain tasks so performing those tasks in the database can lead to performance gains, especially if it reduces the amount of data that gets moved from the database to the application. Another advantage is that the logic is available to any client that accesses the database, e.g. you can access data in an application, a scheduled export and a web report can all make use of the same logic when executing the same sprocs.

That said, putting business logic in the database seems to go directly against the separation of concerns that is the aim of many design patterns. You can also put the logic into a DLL and use it from multiple clients that way. There are certain things that a database will be worse at than applications too, so putting that logic into a database will result in a performance hit. I certainly agree with ntg is that you really should take a hybrid approach, but if you try to put individual logic where it will be executed most efficiently can lead to just that. For that reason, I would tend to recommend against putting your business logic into sprocs. I would agree with szlamany that upgrading an app by running a script is appealing, but tools like ClickOnce make upgrading multiple clients much less of a chore so it may not be as big an advantage as it was.

[upstream]

Thanks everyone!

I will probably use techgnome approach. for single row return and does not require loops, i will just put the logic in the stored procedure, other than that i'll put the logic in the application level. I will just properly document my source code so it wont be confusing.