Even the "don't have to redeploy" argument falls down with clustering or HA scenarios.

One could just as easily argue that means "ok then we use only dynamic  languages from now on and just edit directly on the production server".  That's basically the same argument (though I know people who misuse dynamic languages in the same way).

It's the ultimate red herring argument in IT right now that sprocs are even relevant to whether an application is successful.

I kind of agree with this point (to the point that I find CRUD stored procedures unnecessary and tend to use strong ORM instead), but there are special cases, where you want to use SPs that do something more.

This is important especially when you do some kind of processing that would not be performant enough when done in a different way. In some situations you need to bring your logic close to data, because, well, it operates on large portions of that data.

spoken like a true application developer....lol

Agree 100%.

I just finished working on an application where a previous "architect" added business logic into the views, on all views added triggers that executed stored procedures with other business logic. Complete nightmare with horrible performance.

The argument for that design was the "reusability in case some other application needs that". obviously it was so bad that the main application could hardly use it.

One reason to put biz logic in the db is that you have everything in one place, instead of having pieces of code all over, and if you practice some type of discipline it could work better than having pieces of your biz logic separated and floating all over the place.

The quick deployment thing seems particularly noteworthy.  Wherever business logic lives, whatever language it's written in, it's still business logic, and still important.  If deploying patches to the code is slow because there is a testing/approval process for the code, why is it okay to ignore that process for some arbitrary part of the application?  And make no mistake, if there is business logic in the database, the database is no longer just a "dumb" persistence layer, but an integral part of the application that by all rights should go through the same process.  If this process is onerous and developers feel a need to exploit holes in it to get their work done, the procedure itself needs to be evaluated.

Thank you for submitting this cool story - Trackback from DotNetShoutout

You've clearly never been a DBA or been on an ETL-type project.  I think this rant is pretty ignorant.

T-SQL does many things better than managed code.  Have you ever needed to move GBs worth of data from one place to another in a solution that involved both T-SQL and managed code?  Trying to do it solely through managed code would be negligant, IMO.

Hiding business logic: unless your developers are incompetant, they can be taught very easily to find business logic in T-SQL.  Ever heard of 'sp-help'?

Making it really, really hard to reuse any logic which is inside of the procedure: sprocs can reuse logic.  This is, in fact, pretty standard, something I've dealt with at almost every client recently.

Throwing testing to the wind:  Only people like you who are ignorant of testing outside of your conceptions of what testing about would say something like this.

Seriously, Derik, this is an ignorant rant.  If I didn't know you personally, I would say that you were incompetant to work on most of the clients I've worked with the last 5 years.  This is just stupid stuff.

I work at a company that has extensive Business logic in our sql and I hate it. However, the one point that someone pointed out to me is it can be great for reports. Granted this has nothing to do with business logic in CUD just the R but if you don't put it in your SQL you must either - report off your objects - great for small datasets, awful for huge amounts of data (Rocky of CSLA.NET even recommends reporting straight from the db) or repeating your logic inside the reports.

But you are right we do put way too much logic in SQL. A further disadvantage I find is that stored procedures have no strong relationship with each other. The only way to say that 2 procedures are related is to put a prefix on them. while in the business layer we can put them in the same class.

There's a horrible, horrible middle-ground between putting everything in stored procedures and processing the data in a more OO manner. There's the dynamic-business-data layer. Basically, dozens of classes wrapped around a temp-table, which is defined and managed all in the application using dynamic sql. !@#$% So, there are worse things than putting it all in stored procedures. :-) I don't care if it's all in procs or all in a domain model, make it consistent and code it using the best practices of that style. Procedural, Functional, OO, whatever.

I can't wait for the day that object databases become mainstream.

While I'm one for layered design, performant related reasons do sometimes outweigh the need to be so strictly layered. Often placing business rules inside of stored procedures allows a more performant set based approach to filtering that otherwise would be done in managed code. For instance, if I have a business rule that dictates I show a months worth of data in some UI, I would want to apply the date logic within the stored procedure instead of having some layer filter out irrelevant data. The procedure can accept parameters which dictate the rule, and hence, the interface to the stored procedure can still test the business rule. It's difficult to argue the side of "never do this", especially when there are valid reasons to do it.

I can see the point for putting some business logic in the database especially when it deals with a larger dataset, but a 2500 line insert / update trigger on one of the main tables in my database?  Thank you, but no.

I agree with you entirely.  I see WAY WAY too many developers (NOT DBAs) putting everything and the kitchen sink into the database layer.  Usually they do it in the worst possible way and the entire database suffers as a result.

There are valid reasons when and where to do this.  But having a developer stick everything in the database is just dumb.  

The user above who said this rant was ignorant  - meh.  

/clap  - I agree with you.  Keep up the good rants.

I guess thats why they call them rants, becuase its a preson just blowing off steam...

Business logic in stored procs has its place.  For example large batch applications that will not meet performace requirements through managed code.

Pingback from  Czy ty te?? nadu??ywasz procedur sk??adowanych? « !FrAgile Thinking

I concur with Chris: a major exception to the "no logic in SPs" rule is reporting. I work for a government agency in Florida, and we have to give large reports to the state. Doing these reports in code would take eternities.  Bulk data manipulation isn't really feasible with most ORMs, and even with standard DataSets, it's ugly.

Not just that, but why does Microsoft continue to push code into Stored Procs via all the new capabilities in SQL 2008 and on forward into the future.

Keep your logic separate from CRUD.  Stored procs are only good for CRUD.  Maybe one-offs for extreme cases for calculations but one can make an argument that it is no longer inefficient to do your logic in OOP as opposed to hard core calculations for reports in SQL.

Coding logic in SQL is horrible.  Do that in your C#.  Who wants to debug a pile of SQL Shit.  If you like headaches, go ahead...it's just plain ignorant and stupid to create spaghetti code in SQL and endless loops and nested piles of statements that can't be managed.

>>>I concur with Chris: a major exception to the "no logic in SPs" rule is reporting. I work for a government agency in Florida, and we have to give large reports to the state. Doing these reports in code would take eternities.  Bulk data manipulation isn't really feasible with most ORMs, and even with standard DataSets

I hope you are not talking MS Datasets.  That's  your first  problem, don't use them.  Code custom classes and keep your BL in your BL

And by now, if you do not know better than to keep logic out of procs, you shouldn't be a developer.

in most cases i agree with this opinion.

but when you need optimization for large amount of data or you have many application(servers) going to the same database i guess that you can do the database manipulation in SP.

but as i said in most cases you can do it by "normal" reusable coding.

Pingback from  It is SQL (Structured Query Language) NOT SBQL (Structured Business Query Language) - Derik Whittaker « the antfactory

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Yet another uninformed rant, congratulations. I here this over and over again from developers. It's a little like a cyclist complaining about car drivers and vice versa.

You ain't a DBA or a SQL guru are you? This is why you don't get it.

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

Miriam

www.craigslistposter.info

Do u ppl know about server n client side?

Storing logic in SQL helps a lot. Faster n quick data management as it is runned on server. SP is compiled object which also make process faster.  There are many other points...