Rob argues that ad-hoc SQL is brittle. He further argues that using SPs enables the data model to change in significant ways without impacting the application, if the SPs isolate the application from those changes.
Frans counters:
You can use stored procedures or ad-hoc queries, you have to change the calling code to make sure that column gets a value when a new row is inserted. For Ad-hoc queries, you change the query, and you're set. For stored procedures, you have to change the signature of the stored procedure, since the INSERT/UPDATE procs have to receive a value for the new column. This can break other code targeting the stored procedure as well, which is a severe maintenance issue. A component which generates the SQL on the fly at runtime, doesn't suffer from this: it will for example receive an entity which has to be saved to the database, that entity contains the new field, the SQL is generated and the entity is saved.
The part of this comment that I vehemently disagree with is that changes to the signature of a stored procedure will break your application. Other comments to Frans' post continue to argue that changes to SPs will break client code. While it is possible that any change to the database will break client code, I would argue that SPs do, in fact, enable you to change the database without having to change the application. Let me give you an example.
I was working on a system that would identify objects using a GUID rather than an integer ID number. This was important because, on occasion, the application would be passing object identifiers in a URL in the ASP.NET version of the application, and using GUIDs ensured that someone could not easily try guessing an alternate value. For instance, if a user is working with OrderID 5, it does not take a master hacker to change the URL so that it points to OrderID 6 to see if anything interesting was available in that other order. Other security methods could be employed to prevent this sort of poking around, but for this project, the GUID method seemed best.
Later, a different application was accessing the same database, and for a number of reasons that system was to be written keeping track of the IDs rather than the GUIDS. I simply added an ID parameter to the SPs (with a default value) and the old and new code coexisted without any breaking changes to existing client code. More important, both applications were going through the same database code, so any rules that needed to be followed when accessing data were applied to both systems.
I follow the same sort of rule that was applied to interfaces in the COM world. When an SP goes into production, no breaking changes can be made to that SP. If there are changes required by some new code that will require signature changes that cannot be overcome using default values for parameters, I simply create a new version. Likewise, I create a new version if there are significant internal changes to an SP that modify what the SP does in a way that will compromise client code. So rather than break spTest, I create a new SP named spTest2.
Frans also argues:
Now, let me add something about performance here. Say, you have a database with 100 tables, with an average of 7 fields per table. This requires 100 update stored procedures, at least (the crUd procs). These 100 procedures will have on average 7 parameters to update all fields at once, because you can't create optional update statements in stored procedures.
This is just not true. By allowing default values for SP parameters, you can properly send only the parameters you need, and use default values and IsNull() to update a table with some values missing.
So how important is it that changes to the database might require changes to client code? Regardless of whether you use ad-hoc SQL or SPs, you may have to change client code at some point. How difficult this is varies greatly. If you have a single server ASP.NET installation, making changes to ad-hoc SQL inside an application or making the changes to an SP may be relatively simple. If you have a cluster of Web servers pointing to a single database server, changing the client ASP.NET code becomes more difficult than changing the SPs on a single SQL Server. And if you are maintaining (as I am) a fat client (or as Microsoft loves to call it, a “Smart Client”) application running on hundreds of desktops across a number of towns or counties, any change required in the client application causes an unacceptable burden.
At a prior employer, I supported an application in which any change meant that literally hundreds of CDs needed to be burned and distributed across the country. Making the changes in SP logic in SQL Server and sending the script changes to the customers to apply centrally is a better solution. Note that one of the improvements in maintaining SQL Server these days is Red Gate Software's SQL Compare. I currently keep two copies of most databases I deal with: one that exactly mirrors the structure of the customers' database, and another that is used to make development changes. When I want to make the changes live, I use SQL Compare to create the change script between the development server and the copy that is like the live server, and ship the script out to the customer, safely moving the changes to the customers' database.
One wild card here is that in some organizations developers are not allowed to manipulate SPs or other objects on the SQL Server directly. In this case, for non-technical reasons, SPs are not a good option.
As far as maintenance goes, regardless of whether you use ad-hoc SQL or SPs, an unskilled person will create a maintenance nightmare; a skilled practitioner will do a great job. I have also created server-side installs that automatically update clients when the client code needs to change, eliminating the need for every client machine to be touched by someone. One click deployment will also be helpful in this respect, once it becomes widely available.
Maintenance: For me, SPs have a slight edge in convenience.
Comments