No, stored procedures are NOT bad

re: No, stored procedures are NOT bad

Iceteh — Fri, 02 Jul 2010 09:52:31 GMT

There are 2 main reasons I don't like to use stored procedures that Adam Machanic didn't cover. Stored procedures are storing to much business logic. Aren't OOP tries to separate business and data layers? And stored procedures definitely don't handle business logic as good as application. Secondly, stored procedures is way to hard to debug compare with application.

re: No, stored procedures are NOT bad

Adam Machanic — Tue, 06 Jul 2010 14:08:56 GMT

Iceteh: Thanks for your comment. Naturally, I disagree with both of your points.

You assert that stored procedures are "storing to [sic] much business logic," but you've not defined the term "business logic." And if indeed the procedures have been tasked with too much, said logic can be moved out to another tier. This is not an argument for or against stored procedures; it's simply a question of where to put things. If you've put too much in the procedures, that's your fault as a developer, not a problem with procedures themselves.

Second, you mention that stored procedures are more difficult to debug, and I can't agree here at all. I would say that they're equally difficult to debug, and require a different debugging methodology. This is of course purely subjective, and so I doesn't really apply to a debate on use of stored procedures. A SQL Server professional with zero application development background is going to have a much easier time debugging a stored procedure, whereas a developer with little SQL Server experience will have a much easier time debugging the application code.

Your application will still require use of SQL either way--be it generated or otherwise--in order to pull data from the database. And at some point you might need to debug this SQL. So whether it's in a stored procedure or not seems to have no bearing on this discussion. Assume the same exact SQL can either live in stored procedures or in the application layer--then read my arguments here, and at Simple Talk and they'll make more sense.

http://www.simple-talk.com/sql/t-sql-programming/to-sp-or-not-to-sp-in-sql-server-an-argument-for-stored-procedures/

re: No, stored procedures are NOT bad

Peter — Tue, 26 Apr 2011 05:51:51 GMT

for a erp application we use a sp or query in application

re: No, stored procedures are NOT bad

Sipununu Saporo — Wed, 06 Jun 2012 13:55:51 GMT

SQL is very bad. I hate it. Thank you.

re: No, stored procedures are NOT bad

Adam Machanic — Wed, 06 Jun 2012 14:13:35 GMT

Sipununu: Me too. I'd rather spend my days sunning myself on the beach and dating supermodels. But alas, we play the cards we're dealt, and at least SQL pays the bills. Cheers!

re: No, stored procedures are NOT bad

Surreal — Wed, 03 Apr 2013 12:52:37 GMT

With the use of IoC, I am able to create a separate data access layer that is never referenced by the Business Logic Layer. The coupling between the application and the data access layer is completely removed. Now that we're no longer using SPs, we can now have all of the data queries, inserts, and updates under unit tests.

In your example, if you change the entire database schema and add/remove certain properties... there is no way in knowing that your database SPs are still working correctly without a manual test of said SP. Without using SP's, we are now able to have an assembly or two control everything that SP's normally would, but everything is tested. This has saved us countless hours in which we used to waste debugging SPs.

re: No, stored procedures are NOT bad

Adam Machanic — Wed, 03 Apr 2013 14:42:17 GMT

Surreal:

How does decreasing coupling between your business layer and data access layer have anything to do with using stored procedures? Your data access layer is still tightly coupled to the state of the database at any given time. It doesn't really matter how you're populating your business objects. (Although I certainly agree that reduced coupling between business and data layers is highly desirable.)

It's odd that you feel that stored procedures require manual tests any more so than your current data access layer would. Can your unit test framework not handle a database call? A standard unit test should be able to do the job, and if that's not enough there are several testing frameworks for stored procedures. Google around for them. At the end of the day your data access layer is building SQL calls, and stored procedures make SQL calls, so you're essentially testing the same thing, just getting there via a different path.

What you've done is moved the logic to a place you're more comfortable working with -- and that's fine if that's what works for you -- but to be clear, this doesn't change the dialog nor the debate in any way.

--Adam

re: No, stored procedures are NOT bad

Surreal — Wed, 03 Apr 2013 17:35:41 GMT

It has a lot to do with Stored procedures. For example, in my scenario I would have to recreate a new concrete class that is meant specifically for updating data through a stored procedure in my application layer. If I use Linq2Sql or Entity Framework, this can all be done in the data access layer which is not referenced in my application layer at all.

A stored procedure query is read only, while a Linq2Sql or an Entity would simply need to alter whatever it needs to alter and then it just needs to be updated. I also would need to have a concrete class specifically for the data model and then a separate class that would handle all of the CRUD operations. I don't want a read-only concrete class to be able to update itself, because it can't. In essence, I would need 3 classes just to use a stored procedure on a business object. That makes things extremely complicated compared to the one class if I were to use Linq2Sql or EF.

I'm not saying Stored Procedures are completely bad. They have their place, but you can't say Stored Procedures are a MUST and in many cases inhibit the ability of the application to change. I usually use Stored Procedures for specific reporting. I avoid them when possible because of the complexity it brings.

That and when I see a database with 500+ stored procedures I cringe because I know something is duplicated or just a variable difference in a query.

re: No, stored procedures are NOT bad

Surreal — Wed, 03 Apr 2013 18:14:45 GMT

"I'll re-state my conclusion: Stored procedures are necessary for proper object-oriented database-based application development. Not using them will force code duplication, tight coupling between applications and databases, and greatly increased man hours for every change made to either application or database."

Using stored procedures will force duplication since many queries are only slightly different and can slightly alter the read-only data of the business objects. Higher level business objects may only become partially filled.

There is no tight coupling between applications and databases. My applications don't even know a concrete Data Access Layer class is ever created because of IoC. They just know of their business objects. How they get populated and updated is of no concern to the application. A change in business objects will require every application to be updated that is shared. That is true like you have already stated. Same would be true with Stored Procedures. To say SPs are necessary is not true, since any change to a database schema like changing a value from an integer to varchar, for whatever reason, would only affect my one data access layer.

If my company were to switch from MSSQL to MySQL, I would need to rewrite a data access layer like you would have to rewrite SPs. My applications wouldn't know anything ever changed. The IoC would simply get pointed to the correct assembly.

Stored Procedures are NOT necessary. That's all I'm saying.

re: No, stored procedures are NOT bad

Adam Machanic — Wed, 03 Apr 2013 19:44:52 GMT

Surreal:

I'd forgotten that I was quite so rigid in my conclusion. Keep in mind that I wrote this almost 7 years ago. I will agree that today's ORM solutions do make it far easier to create a robust solution without stored procedures than it used to be. I think were I to write this today it would be a very different article.

These days I only very rarely use stored procedures for data access anymore, but I do make heavy use of inline table valued functions in their place (which I think of as having many of the same benefits, with some added bonuses such as output contracts). I still heavily use stored procedures for data updates; but in my world updates are not usually simple CUD, and require fairly significant updating data

One thing that I think is special in your case is that you have only one data access layer for the database. In the project I'm currently working on, for example, we have at least 5 different applications, written in different languages and running on different platforms, all sharing the same data. It would be impossible to create a single data access layer. Maybe an enterprise service bus or fabric model could be used, but that has its own headaches. The TVF layer I've created works nicely and allows me to shield all of these apps from changes when I need to make them in the database.

I do not agree that lots of stored procedures means lots of duplication. That is certainly not the case everywhere, but I know that there are a lot of really bad databases out there. I suspect that a lot of the developer disdain for stored procedures has to do with the fact that a lot of people write them who really have no business doing so.

--Adam