**** Warning, a rant is about to take place ****
Can I ask a stupid, rhetorical question? Why do we continue to put business logic inside of SQL Procs? Can anyone answer me this? About the only half way decent answer I have ever heard is:
Because we have some of this type of logic (Business Logic/Rules) inside of a sql proc we are able to update/change/modify this rule on the fly without the need to redeploy assemblies.
I am sorry, but this is about as lame of a reason to do something as I have ever read/heard. Yes, I will admit that when you do put logic into your procs you do not need to redeploy. However, you are making this decision at the expense of other, better decisions.
When you put your logic inside your procs you are doing many, many things which I believe are bad.
- Hiding business logic
When you put your business logic inside of a proc you are now making future developers ‘hunt’ for this information. As developers the first (and should be only) place we look for business rules/logic is in the code. When we do not see logic in the code we are stumped.
- Making it really, really hard to reuse any logic which is inside of the procedure
Because this logic is put into a ‘single use’ object like a stored proc there is no way you can reuse this logic. This leads to code duplication, which leads to bugs, which leads to shit going bad. If the logic inside the proc was to be placed in some reusable business class then you can reduce possible bugs when the need arises to implement this logic in multiple places (and we all know this happens)
- Throwing testing to the wind
We all know (or I hope we do) that we need to test our code (yes I am talking via a testing framework, but this cause also mean manually). When we put logic inside of a proc testing gets very, very difficult. About the only way you can test this is by running the application and executing the proc. Although this approach could work in a limited capacity, it is very error prone and very hard to repeat with any success.
If I had my way SQL as a language would no longer support items like ‘If/Then/Else’, While loops, case statements, or pretty much any other logic items which ‘make decisions’. Sql syntax and usage should be used for one purpose…… Manipulate data in the database. And i define this as, Read (straight up selects) or Add/Update/Delete.
Till next time,
02-28-2009 3:02 PM