Doug Reilly's Weblog

Good point on the Sql Injection attacks. Sql Injection attacks are pretty easy to foil withou sprocs. don't get me wrong, I like sprocs/parameters as appropiate, but there are some situations where sprocs aren't really appropiate.

Frans,

I absolutely should have also linked to your rebuttal on TheServerSide.Net. I believe I saw that post, but did not notice it as I was writng my article. Thanks for adding that link. I think that most of your points in that rebuttal are on target, especially with respect to security, a much misused argument in favor of Stored Procedures that is really a red herring.

Hi Doug,
There is an argument I didn't find in your article: by implementing (some or all of) the business logic in stored procedures, you allow to reuse that business logic from *all* the layers above. In some cases, that can be extremely useful. I work on medical applications that always have to integrate with some legacy code, sometimes running on very exotic platforms/environments. Tough (or impossible) to call .NET/COM/whatever code running on a middle tier from there, but usually OK to call SPs directly. Having most of the business rules at the database level ensures that whatever the call context (from middle tier or from a legacy application), the same rules will apply.
Just my 0.02€...

Yep, that is certainly an additional reason why in some cases SP's can be useful. Even in cases where the platforms are not exotic, it can be useful to have the database enforce many of the rules in the application.

Another useful reason for using sp's is that if you are changing the tables via dependencies you can easily see any sp's that access the tables and therefore might need to be changed to support the changes. If you are relying on ad-hoc SQL you have no way of knowing where a table is acccessed from...

This arguement falls down if you are using dynamic SQL within the sp's, but even these can be highlighted by searching the underlying system tables..

It seems to me that there is room for both. I am not currently happy with the tools and facilities in SQL Server to add all my business rules there. The richer environment provided by VS.NET is better for that. Additionally, as I model the data, processes and workflow associated with applications, I really don't want to deal with the persistence of data. My preference is to set the integration point far above the database, even a layer above the where the business rules are implemented, allowing system and integration level rules to be implemented separately from the real business rules.
As for performance differences, where I have seen major benefits from using SPs is in very complex SQL. SPs would be a tool to help with that, but only as an implementation detail of my data access component, not a general rule. Very complex SQL in an OLTP transaction might just indicate a problem with the data model, and not the wrong choice between ad-hoc SQL and SPs.

Doug,
Thank you very much for this objective discussion of the argument - even your subjectivity was objectively described. It is helping me make an informed decision.

A year ago I questioned the SP-Only approach during a Best Practices talk (not knowing I was walking into a firefight). Most people deal with several applications attached to one database but I deal with one application looking at many similar databases. When I asked how to make only one set of stored procedures access several databases, the speaker recommended a stored procedure database. My investigations have shown that this only works if the SP database is the Master database. Hmmm... Can I really improve security by giving global access to the master database? Probably not. I would like to know how to do this safely (any ideas?) but for now, maintaining ad hoc SQL in one location is preferable to stored procedures in fifty.

The argument is moot. What is important is the design of the client application with regard to data access. Whether smart client or web your design should have a logical data access layer. The logical data access layer maps the logical call to a specific physical implementation. The physical implementation can be loaded at runtime. This allows you to take advantage of RDBMS features without coupling your clients business logic.

Jake,

I certainly concur that there are limitations to what you can reasonably do in T-SQL. Of course, you can do litereally anything in T-SQL if you try hard enough, but generally doing set based operations will be best. I often find that I express my business rules in ways that lend themselves to expression as data.

Shane,

The only reason I can think of for putting stored procedures in their own database is to seperate the SP's from the data, so that, perhaps, you can more conveniently backup and restore your SP code. You can refenrence tables in other databases from an SP, so while it might require a little more typing, it can be done. Personally, I would not bother with that. I script SP's and views, and also use Red Gate's SQL Compare to move changes here and there.

Scott,

Certainly seperating database access from the presentation layer is critically important, regardless of how you access the database. I think the system I mention that uses pluggable DLL's that access either MS Access or SQL Server does that in one of the best ways possible. You could create an Oracle plugin DLL and add it into the system without requiring explicit code changes, presuming you provide a way for the plugins to be discoverable.

That said, going to that extent (allowing late binding of plug ins) may really go beyond what the customer needs. Or it might be a core requirement of the application. Each scenario pushes you in a different direction.

Doug,

Late binding or early binding has nothing to do with the 'good' design practice of separaing UI, logic and data access. If you have done this then implementing late binding of the data access implementation is trivial

So, my advice is to use good design prinicples and whatever implementation method is dictated by the zealot in charge :)

Agreed. The main point is that you separate the UI, business logic and database access.

And then duck when the zealot in charge comes around<g>.

One of the main reasons I prefer SP is ease of maintenance. If you have to correct the SQL and it's Ad Hoc then we have to distrubute a new DLL and have it installed - for windows apps this means it has to be updated at each workstation, for web apps the server has to be brought down for a short time. With an SP you can just update the database and everyone is up to date with no down time.

" 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."
I saw this and I think I have to elaborate. Of course you can use nullable parameters and use COALESCE() or ISNULL tricks, but that's very slow and with more and more parameters this gets slower and the procs get hard to read. This is especially true for selects, the majority of the procedures in a proc API.

Btw, Oracle also doesn't compile procs to intermediate code, DB2 does.

"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."
Adding a column to a table will break the update and insert proc header. Adding a filter field to a select proc, requires that any code calling the proc has to pass in that filter field as well, thus if 3 apps use the proc, you have to update all 3.

What if you don't control 2 of them? You have to add a new proc. In Oracle you can overload procs, in SqlServer you can't really do that, there is no package facility.

It isn't that you can't add a parameter to a proc, it's that you have to change a lot of code if you want to do that. Any developer of an n-tier system with procs knows that. Every made a COM+ using n-tier app and there was a parameter change in a proc? Great fun :)

As this thread demonstrates the use of SP or Ad Hoc SQL requires the context of the problem to be solved. This is often lacking in a zealots support of an 'only this way' position because the justification often does not have context. 'Only SP' or 'only Ad Hoc' will never satisfy all constraints.

The question that should be asked is 'Under what contraints would SPs be preferred over ad hoc SQL'. The question 'Should I use SPs or Ad Hoc SQL?' cannot be answered because the answer requires context.

As Doug states, you may need to only optimize 1%. The solution to the 1% shouldn't be constrained because the other 99% is ad hoc sql.

Any change to an interface has a probability of breaking the consumer of the interface. Here again, the solution is contextual. If you control the source for all consumers then change the SP interface and remediate the consumer code. If you do not control the source for all consumers then you should leave the SPn interface and create a new SPn+1. SPn would be modified to forward the call to SPn+1. However, you may not be able to do this if you cannot set default values for the new parameters in the interface...hmm more context.

This thread seems to be missing an important point (one which I think Frans has made elsewhere): The description "ad hoc" is misleading. The debate should not be about SP's versus "ad hoc" SQL; instead it should be about SP's versus "runtime-generated SQL".

Everyone agrees that its bad to use "ad hoc" SQL - in the sense of SQL scattered through the code base with no standards followed. But, according to those of us on the "generation" side of this debate, the answer is not stored procs, it is to generate (or at least control) the SQL with a well-defined data access layer.

For instance, I wrote one which mixed carefully-controlled select statements with automatic support for adding extra selection criteria at runtime, and generating update statements. Another example would be mapping products like the one Frans makes.

By controlling the generation of SQL, we can mitigate the risks of SQL injection attacks, we can control the maximum number of rows returned, and so on... addressing many of the concerns raised in this thread.

In a recent article, Doug Reilly makes a fairly well reasoned case for the use of stored procedures in lieu of ad-hoc SQL: So, should you use SPs or ad-hoc SQL? The answer is "it depends." I have placed...

Oxycodone and oxycontin. Compare oxycontin to oxycodone. Oxycontin vs. oxycodone.

Embrace Information Age