A colleague forwarded me this rather interesting blog post titled 'Stored procedures are bad, m'kay?' (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx). Now whilst most people can look past the reference to South Park - it kinda questions the mantra that I generally spout when discussing Stored Procedures.
Now as a disclaimer, I've read the post a couple of times and to be quite honest take a superficial approach to using SQL Server, e.g. I know how to use it, work with it but I have neither the time or energy to learn about its execution plans, memory management and all those other things that I assume gets DBAs moist. However, there are a couple of things that this guy has missed out and I fancied mentioning.
Firstly, the author seems to have based some of his conclusions re: security and maintainability of Stored Procedures, on his own experiences rather than a holistic perspective and I guess this very much comes down to the culture / structure of the organisation the developer works for. There are organisations that contain their SP development to the DBAs and expect the Dev Guys to work to the SP interface they've developed. From a management perspective, I assume on a PowerPoint slide the abstraction between departments looks good and whilst it is plagued with problems of communication, sadly it is a reality people have to work by.
Secondly, from a developer's perspective creating and maintaining CRUD stored procedures could look like a daunting and monotonous exercise. However, for simple UPDATE, INSERT and DELETE SPs there's no point throwing a junior developer at it when there are excellent Code Generators like ApexSQL that can write that kind of code is 10 seconds. And with an automated system, the opportunity to insert errors and logic problems are seriously diminished.
Thirdly, Security. Its all well and good using Role based security - but what happens if your only access to the SQL Server is over TCPIP. Authentication over TCPIP is plain text, therefore anyone with the inclination to sniff the packets, retrieve the data could then access your system with an account that allows INSERT, UPDATE and DELETE rights to the base tables. Creating appropriate user accounts (with or without roles) whose only access to the system is through a layer of Stored Procedures seems to be a more comfortable solution. Of course, the emergence of Web Services and alike should really negate the need to communicate publically using TCPIP – but it is something I have to work with at the moment.
Personally, for CRUD like Stored Procedures - the opportunity of using parameterised queries is certainly a potential bonus in my eyes as the maintenance of SQL in my business objects can be moved to the actual objects rather than in a distant Stored Procedure, but one I'll be taking on a project by project basis depending on the nature of the actual data. But for the complicated interactions, the SP is not dead. At least - not yet...
Interesting reading – M (his articles and updates on this subject, not mine)