The cross-platform tie-in argument
One argument against using SPs is that they tie you to a particular database platform. But is that always true? Neither Rob nor Frans address this issue in the posts I reference.
I know of one developer who created an application that can be used in two modes. The program can be operated on a LAN with all users hitting the same SQL Server which uses SPs extensively), or it can be operated independently, where the database is Microsoft Access (which does not use SPs). The program runs all database access through a known API supported by a pair of pluggable dynamic link libraries. There is added complexity, since changes need to be made to both code bases at the same time. For this application, the tradeoffs were well worth it.
Can an application be too flexible? The answer is yes. If you don't use features of your chosen database platform because you might need to move to a different database someday, you are probably giving up too much. I have built many applications that had to account for the possibility that someday a different database back end might be required. I have never been called to make that retrofit. If multiple back ends are not an initial requirement, I would not worry much about it. I would, of course, partition the application so that database access is handled by classes separate from the presentation logic, but that is a good idea no matter what.
Cross platform tie-in: Ad-hoc SQL has the advantage here, but do you care, and is there a good way to overcome the limitation anyway?
Conclusion
So, should you use SPs or ad-hoc SQL? The answer is “it depends.” I have placed myself firmly on the side of doing all database access through SPs. I do so knowing that I am not getting any unique security benefits using SPs, knowing that the performance benefits are not as clear cut as I once might have thought (but are still real in some cases), knowing how to leverage SPs to minimize the maintenance load, and understanding that I am more tied to SQL Server than I might be if I were to use ad-hoc SQL. What do you think?
###
Doug has a blog post on this article, and comments or questions can be posted there: http://weblogs.asp.net/dreilly/archive/2005/03/30/396251.aspx
Comments