In Defense of Stored Procedures
Based on the comments regarding the relative benefits of stored procedures versus so-called in-line SQL a these blogs: Rob Howard's and Frans Bouma's, I decided throw a little something together here in defense of stored procs (and Rob to a certain extent). Some of this will be my own thoughts, and I'll share some comments made on an email list made up of mostly AspInsiders and MVPs (and all experienced developers). First, I'll paraphrase the comments of others.
Paul D. Murphy says:
The argument Frans presents against stored procedures based on portability is not valid, especially for enterprise databases. Once you start to deal with real data needs (millions of records, TPS rates of 300+, etc..), you always will need to take advantage of database-specific table/row locking keywords or database-specific usability features.
So if you can't use the same dynamically created SQL statements, what's the point in not putting the data logic in the database? Microsoft's abstractions found in the various data library interfaces are the most robust way to achieve portability across different database providers. It's very simple, code your DAL factory against the interfaces, code your DAL objects against a strong provider, and use reflection. Don't drop to the lowest common denominator (ANSI SQL) cause that will only give you a DAL that isn't scalable.
In enterprise scenarios, if Joe Programmer decides he wants to get some data based on a pattern match in the address line of a record under normal conditions (200 transactions per second, indexes tuned for zip code and phone number searches and NOLOCK for straight lookups) this simple statement will crash an enterprise database.
Sprocs are more robust; so you can do more with them naturally. But the same basic rules apply. You can't arbitrarily change queries on 'real' databases. Normalization/Denormalization is a data function that physically needs to exist in the database product. Abstractions and indirections are the keys to successful scalable programming.
Short cutting the rules might work, but that doesn't make it the right way to do it if growth, code longevity and scalability are important factors.
Shared by Phil Winstanley (though not necessarily his own opinions):
The arguments for inline SQL do not consider if your data-consuming applications are not in the same environment. Writing stored procedures allows any environment that can interact with the database to use your data access code.
Stored procedures can be used to form a contract between the database and the consuming applications, and storing data access code on the server gives you a centralized place to maintain that code, instead of searching through various components that could in turn be interfacing with others.
Whether or not you use stored procedures, if you change or add columns, for example, you will have to modify your code. Using in-line only does not help much in this case.
From Steve Schofield:
Being from the admin side of the planet, I've have to support applications that both use stored procedures and don't. "In My experience" applications don't perform nearly as well and have had to purchase more equipment to scale vs applications that use stored procedures. Now that doesn't say applications that use dynamic sql don't scale as well. I just have to live the pain everyday of throwing more hardware at enterprise applications that don't use stored procedures.
And now for my own thoughts on the subject:
One thing that's not really addressed in the arguments that I've seen is the readability of DA code when you use in-line SQL. You have to put it all in quotes and on multiple lines, storing in strings, etc., or you build it bit by bit on multiple lines separated by a lot of flow control. It makes the resulting SQL damn near impossible to read w/o profiling, and sometimes you can't profile, so it's hard to debug as well.
Stored procedures, on the other hand, can easily be opened up in Query Analyzer and debugged there, substiting values for params where necessary. Then when you've got the query working like you need it to, you can pretty much just copy and paste back into your stored proc, and you're done. There's no need to figure out how to divide it up into the various pieces or split across many lines or replace quotes here and there, etc.
Putting the code into a stored proc makes it very easy to read, generally, especially with some tools, like VS.NET, that block out queries and color code, and so on.
Further, making any tweaks to an in-line query in .NET (or Java) would require a recompile and redeploy of the related data access code. Often you can make many tweaks to a query without it breaking the parameter interface. You can do this without needing to recompile and redeploy your application. This point is particularly poignant when you have disparate components consuming the same data access code. In this sense, ad-hoc/in-line queries are definitely brittle as compared to stored procedures.
So I think the arguments that in-line SQL is more developer-friendly are invalid in many cases. Add to the points above the fact that you can often off-load much of the data access code onto a DBA (who is likely better at T-SQL and performance tuning than you), and there really is no comparison, in terms of developer friendliness.
And even if you write your own, using stored procs keeps you honest if you have a DBA who goes over them to make sure they're not going to adversely affect the databases, be it in terms of performance or security. You can also still use the DBA for performance tuning, as he can easily review a stored proc to see where he can modify it or indices or add views, etc. Many DBAs cannot or do not want to read through in-line queries for this purpose, so you'll be on your own. This applies both to development and problem resolution. If you do it all in-line, you'll get the phone call as well as the DBA; whereas if it's all in stored procs, the DBA can probably solve the issue.
Stored procedures are clearly the way to go in these respects.
Now I would like to point out that Frans was not exactly being fair in his blog. He took out a couple points Rob made and presented them as if they were the entire argument, which is not true. Apart from general flaming and blustering, which in my opinion show a lack of confidence in one's position, there are the following to consider.
For performance, Frans, IMO, did not address the first two (and more important) paragraphs that Rob wrote, which deal with filtering, sorting, and multiple-query/batch queries. These are very important and valid points that have not really been addressed by the 'opposing party'. Even though it's true that you can put multiple queries in one command batch, you multiply the difficulties I addressed above in terms of readability, maintainability, and performance tuning. And I'm not sure how SqlCommand would handle parameters with multiple queries or if it'd work at all.
Instead he focused on the issue of caching, which is a minimal point. I am not a SQL Server expert, but most of the SQL Server experts I know (and I'm talking about very experienced developers, MVPs, and DBAs, such as those who work at huge corporations like Verizon that deal with millions and millions of records and transactions) advocate stored procedures over in-line T-SQL. As others have commented, though dynamic queries are cached, they are still handled differently. And in my own experience, I've noticed that they tend to cache for a short period of time. But if we put our code into stored procedures, performance on the whole becomes less of an issue because of the reasons given above (easier tuning and availability of DBA expertise).
For security, we'll ignore the stupid comments made towards Rob. Obviously we all know about roles. The points are still valid. Using stored procedures and views give you better protection and finer control over how your data is accessed and manipulated. Even if you use a view instead of granting a login permission to a table, you still are limited in terms of how fine you can control access, and I've heard conflicting opinions on updating through views, so often folks grant modification permissions to the table itself. Using stored procedures gives you a very fine grain control over what users and roles can do, and you can quickly and easily revoke specific permissions if needed without affecting the rest of the applications' usage of the data.
Furthermore, arguing that you should use only views for fine-grained control throws you back into a management nightmare because then instead of having many stored procedures to maintain, you then have many views to maintain. The simple truth is that using stored procedures, with or without views, will give you finer control (better security) than the opposing position. At a bare minimum, you can see that you can use SQL roles (that Frans advocates) with or without stored procs, so using them as an argument in favor of in-line queries or against stored procs is faulty.
And as for recommending using stored procedures as a way to prevent injection attacks, one must understand the audience that Rob must deal with. These are typically the folks who do not know about or remember to use ADO.NET parameters, so recommending stored procedures is a guard against lazy developers (most of us) who would simply use concatenated T-SQL otherwise. Recommending stored procedures for this reason is entirely valid, though it is good to also point out that using parameters with in-line SQL is also a valid way to prevent such attacks.
All-in-all, I would say whether or not you use stored procedures depends on your situation. There are some cases where it just might not make sense, such as tiny (in terms of amount of code and time to develop), single-developer projects, dynamic query-builders, and the like. But I would say for the vast majority of long-lived, “real-world” applications, especially on multi-developer teams with DBAs, you should use stored procedures for all the reasons given. In the short-run, it might make things go a bit slower, but in the long-run, you will have greater returns on your time invested.