What is the argument for and against putting code in stored procedures with the intention of making the code more maintainable (i.e. Easier to make changes to the business rules without recompiling code)?
All else being equal what makes a stored procedure better/worse for maintenance?
I am not arguing against stored procedures, but I would suggest that they are harder to maintain since they must be updated independently of an update of the application (They must be stored in the sql server).
It probably depends on your system. For us, Stored Procs are used almost exclusively. We just have one website, so having the SQL statements in a stored proc makes it so much easier for our DBA to tune queries and recreate performance causing issues.
in the project i am working, most of the maintenance can be accomplish by only changing the sql procedures. so Yes Store procedures are more maintanable in my case, also they have better performance than executing sql statements from your code.
If your base software is deployed at several different customer sites, much of the customization necessary for each individual customer can be accomplished via the database (views and stored procedures). You can almost think of the SP as an INTERFACE, pass the standard data back and forth without concern for what happens inside.
This can, of course, also be handled in other ways such as a custom .dll data layer for each installation.
In some cases though, the customization in the SP instead of a .dll allows for faster customization and allows a DBA or data expert to take over, leaving the programmer to work on coding.
Remember that the code in the SP is available to many more people (the customer, in this case) than compiled code. This can be good or bad, depending on your situation.
In some enterprises, it is politically easier to change a stored procedure than it is to have software re-installed (that depends more on who has more stringent rules, the developers & project managers, the DBAs, or the IT/help desk ).
Equal, this is bad practice to change on the fly, do you remember?
Seriously, making code in stored procedure can make you sure that your SQL interaction is faster.
But your changes is too far from source-control. (for source control , you need to export, as i know)
Generally, that argument is made because you're making ad-hoc changes to the stored procs instead of going thru version control, testing, etc. that compiled code would go through.
I don't have an immediate knee jerk reaction to that (unlike most folks here do), but I will say that compiling and deploying a DLL really isn't that much harder in that type of cowboy environment. Or, you could use something like CS-Script, which allows you keep raw .cs files that get compiled on demand.
I've always found it difficult to version and test stored procs, whereas most code is easily done. Also, most RDBMS procedural languages are pretty primitive - so you don't the expressiveness or abstractions that a contemporary language gives you.
Of course, version control is a Good Thing - and, for most places, so are some process checks between the devs and production. ;)
I think one factor that influences this is the number of clients as it makes it possible to make schema changes without having to redeploy the apps. E.g. as a DBA I find it much easier when tuning SQL, resolving bugs, etc to visit one Stored Proc rather than X clients to change a SQL statement, when X is a large number and 'visiting' them means deploying a new version of the app to multiple workstations.
Done properly it also means that the schema that the application uses can be different from thedata storage schema. E.g. I like to highly normalise the data schema and provide a set of views and stored procs that the application uses. The apps schema changes in time with the changes to the app code and independent of the data schema. This is a real advantage with multi-application systems (e.g. one read-write app and a web-site app using the same data.)
Often there is a different set of tools and a different language used to write stored procs. This can be a impediment to uptake requiring training, skills development, etc.
I think the main disadvantage, in my experience, is that few developers I've worked with were really comfortable with SQL (writing it in the first place, debugging it) So you have to make sure you will have the right skill set in your team to be able to maintain a lot of SQL.
A big advantage can be if you have multiple platforms (say both windows and web apps) or you port an old app to a new technology, you may be able to reuse the database procedures.
The application I'm working with has a lot of business functionality in stored procedures, and probably half the bugs that we fix are in SQL code, and if critical can be quickly fixed by replacing a stored procedure, as opposed to the bugs in the application code which must wait for the next release. (the flip side though is that we might have had fewer bugs in the first place if we didn't write so much SQL code, who knows!)
Stored procedures are a bad practice for a number of reasons.
One of the most significant ones is separation of concerns. With stored procedures, you have business logic in the data layer, instead of in the service layer where it belongs. One consequence of this is that you now have one language and one language only in which to implement your business logic. As new technologies come along, you have no good migration path.
Another solid reason to avoid stored procedures is that stored procedures create a strong bond between you and the DB vendor. It will be very difficult to ever change to a different DB vendor, whereas having your business logic in the service layer will allow you to very easily swap out DB. So, stored procedures provide great benefit to the DB vendor, and not so much to you.
Next, scalability. It's pretty straightforward to create services in a middle tier, and distribute them across a cluster. How are you going to do that with a stored procedure? I think it would be egregiously difficult to cluster a stored procedure across, say, even eight machines.
Next, integration with other systems. If your system is pulling data from your database, relying on stored procedures, and other data from other systems, that logic will almost certainly have to be in a services layer. If some of your business logic is in the service layer, and some in the business layer, you have something of a maintenance hassle.
My experience is that source control is rarely used as diligently for maintain the database as it is for the application code. Sure, this is probably not always the case, but in 15 years I have never seen it. What that means is that you are more likely to have people making change in dev, and heaven forbid live, database stored procs with no real thought to ease of maintenance because it is just too easy.
Stored Procedures are like crack, they are addictive and harmful. I won't go into well known performance/security advantages but from maintainibility perspective they have these:
I think two things kill all the advantages: version control and business logic. SP's seem like they can handle business logic but they just can't. And yet it's really hard to keep yourself from adding business logic in an SP because it looks too easy, tempting. Putting things in a transaction, running your logic there feels like a warm blanket. Oh you can do it with a join, and that with a union oh that's just great. Then suddenly you find yourself looking for ways to access COM components and web services in a Stored Procedure. Pathetic.
Although there are ways around version control, idea to implement something with SP should always be taken with a grain of salt.
I prefer to not use stored procedures because I like to remain relatively database agnostic. If a better relational database comes along, I'd like to have the option to migrate to it. Stored procedures will lock you in. That said, I do use them occasionally, they can be very powerful.
Think of the alternatives...
Then consider your requirements and environment...
Are your developers familiar with your database management tools or do you have more database programmers and administrators handling the db side of then fence?
Are you required to make frequent changes to the db schema?
How about security? Do you require security all the way down to a database user level? Would it be easier to manage security in code, or in the db?
Would your developers be more productive with an ORM, not having to write a DAL for themselves or is there added complexity that you would like to add in a custom fashion to your DAL that an ORM couldn't provide?
etc...
People are going to have different opinions on whether or not procs are easier to maintain based on what kind of systems and environments that they have worked in that are probably far different from your own. What you should probably be doing instead of just wondering if they are easier to maintain, is figuring out whether or not they suite your needs. It's a really good question, but maybe edit your post and explain a little bit more about your env. and you may get a little more targeted advice.
I've done CRUD programming as consultant for 10+ years in the business world. I can tell you that I put as much business logic in sprocs & views as I can (and makes sense). Requirements tend to change every time the winds blows and having logic in the database makes it easy to change and (with decent comments) it's self documenting. Plus sprocs make for good security and for easy code reuse.
FWIW I use source control and strict testing on all my sprocs. Doing otherwise is just lazyiness.
Stored procedures are a good idea for a number of reason:
By putting business logic closer to the data, you are able to have any number of client interface to the data. You may initially design the front end for a web site but now they want reports. No problem, the business logic is next to the data. Open up the APIs, put a web service in front of the procedure. You want to use the hottest new language, go ahead. The business logic is next to the data.
Another reason to use stored procedures is that it creates a strong bond between you and the chosen database. This way, you can take advantage of the built in functionality of the DB that you are paying for (or using in case of open source). And guess what, if you try to make your application DB independent you will probably have a number of hard to track down bugs. Read consistency works differently on each vendor's database.
You can take advantage of your database built-in scalability (cluster, RAC - however they implement it). No need to write your own.
It is harder to write code that does not use bind variables (google soft parses vs hard parses if you need more info on that).
Version control - the companies I have always used version control for stored procedures. You normally write stored procedures in some editor. Most editors now have built in support for at least one of the major version control systems.
External code has to pull the data across the network and then work with it.
Lastly, not all procedural languages are created equally. MySQL just released the ability to use stored procedures and they give you a number of languages you can use including their own. I doubt theirs stand up to Oracle's or SQl Server's. I'm not going to go into who's the best because I am not an expert in anything but Oracle. Oracle's PL/SQL has a lot of functionality that is optimized in the DB kernel. I am sure MS SQL does that too as well others.
Hope this is helpful (and makes sense - long day).
Stored procedures provide many advantages over keeping queries in the application, and many have been mentioned already.
Stored procedures serve as a layer of abstraction between the application and the data, and layers of abstraction are rarely a bad thing.
You can achieve a certain level of reuse if you have cascading database operations. The Delete_Employee procedure, for instance, can call the Delete_Address procedure in a transaction, and do many other things, with a single database request from the application.
A layer of Views underneath the stored procedures can be used to isolate the application from schema changes and query/join performance tuning.
A stored procedure layer is an important part of a solid enterprise architecture and framework. These stored procedures should contain no business logic, of course, they should only pass through data and not manipulate it.
In order to realize these benefits, good discipline is in order to maintain consistency and the same granularity across all procedures in an application. I have worked on applications with many 100's of procedures. Had all these queries in the code, it would have been much more difficult, if not impossible, to implement data-level security. Stored procdures can be easily generated. I feel that overall productivity is higher with stored procedures than withoug. And of course stored procedures can be in source control, as it is the case with all other database objects.
I'll one more thing about version control. At my last job, I added a 3rd party packaged that ran each day and stored a copy of each object's DDL or code if it changed from the last time it was backed up. It itself was stored procedures so could have been run manually or by trigger, how ever you wanted it to run. It in itself is a version control system.
It was a pretty nice tool and was only about $200. It even came with a diff tool to show you what changed.
We've switched from using sprocs to a generated DAL for code-based business logic for quite a few reasons:
It really depends on how well the stored procedures are written, doesn't it? T-SQL and PL-SQL can get just as gnarly and hard to maintain as C# or COBOL if the practitioner isn't coding with some thought and care.
One of the recent enhancements I worked on, I purposely put the functionality in a stored proc, mostly because I wanted the functionality to be easier to reference, much less maintain (the stored procedure can be called from the DB level and also the client level; if I'd put the logic in a different layer, that would not be true). I thought I'd done a good job of making the PL-SQL fairly modular, but when TOAD analyzed it, 3 of 4 sections got good marks, and the last one got hammered for high cyclomatic complexity. Heh, I think I've just proven my own point!
Some would argue that business logic has no place in Stored Procedures, and results in unmaintainable systems. I would point out that these arguments come primarily from the DDD and N-Tier points of view who seek to house all business logic in a specific region within the application layer. While this is a worthy goal, there are other points of view out there worth considering. SPs can also do a lot more than just house business logic.
Incidentally, the Oracle world believes its best practice to have all business logic in PL/SQL code on the DB, and they know a thing or two about relational databases!
Consider these uses of SPs:
Performance. It is often useful to run multiple SQL statements in an SP and thereby avoid your application making multiple round trips to the DB, resulting in sometimes considerable performance improvements.
Change Management. SPs are simple to maintain and modify, as long as you have discipline around version control and your deployment process.
SPs can be released to live production systems without an outage, which can be a considerable advantage in 24/7 operations. Of course you still need tight control around the release process.
Layer of Abstraction. SPs can abstract away the details of the DB schema from the application, provided all app/db interactions are via SPs. This can be a very powerful concept. Consider that the lifespan of the DB will often outlive the app - apps come and go, and get re-written every so often with the latest technologies and architecture patterns, but the valuable data stays in the same old relational DB for aeons. Frequently, mulitple apps are developed on the same DB, even if this was never the original intent. SPs in these scenarios are used to provide a tight, well defined API between app and DB, which can be carefully controlled to ensure consistant interactions with multiple apps and even multiple versions of the same app.
This separation of concerns between db schema and app leaves application programmers free to do what they do best, while leaving DBA's a free hand to improve the schema over time without breaking apps.
Whatever architecture pattern you employ, SPs can play a valuable role. Don't rule then out of any design, and like any tool, use it where it makes sense.