“The issue is not how many pieces of code will be affected, the issue is how do I find all of the affected pieces of code?”
Try Edit-Find And Replace-Find In Files
This has been a good read for me. I enjoy hearing both sides of the argument and really haven’t had a strong opinion either way. Until now.
I believe the security and abstraction that comes with using stored procedures make them well worth implementing. I know the maintenance aspects have really saved my team on a number of occasions.
Security wise, preventing direct access to the tables make sense. It keeps them from being abused by the general public (or casual developer). Stored procedures create a standard interface that can be enforced, keeping an application developer from unknowingly putting your data into an invalid state.
Although it’s bad practice, stored procedures can be modified to add functionality to a deployed application without re-release. This makes my boss happy because he doesn’t like the time or the process involved in certifying and releasing builds. A stored procedure change can often take less than a few minutes and get an application back up and running. It can also add new functionality to a well designed application.
As far as being able to swap out databases, I think you can still do this with a small amount of effort. If your database supports stored procedures, you’ll have to re-code them in the new database. One plus to note here is that your code should require NO changes to support this. I should say “in theory” but this has been the case for me on almost every occasion so far. If the new database doesn’t support stored procedures (mySql) then you can override your standard datalayer (that interfaces to the stored procedures) and put the logic in there. A word of experience here: it takes a lot more effort to implement logic from a stored procedure in your data layer. One plus of database script languages is that they’re designed specifically to manipulate data. They’re very compact and focus primarily on that function.
Think about accessing the database through stored procedures (only) as a means to using your database like a service. It provides whatever (and only) the functionality implemented by your stored procedures, regardless of what application is accessing it. It you have one application and 4 import (applications) all using the database, stored procedures will help ensure that they all do it in a consistent way. New applications will already have a good baseline of database logic to choose from. You database becomes more like an entity, or single object that has specific functionality available.
If you’ve never tried stored procedures, you owe it to yourself to do so and formulate an opinion based on your experience. Do some more reading on the subject too. Quite often, you’ll find that a development method/practice doesn’t make sense because you haven’t used it in the same way that other people have successfully used it.