Yet Another Stored Procedure vs. Ad-hoc Query Discussion?

It's not a question of performance, security, or SQL injection.

It boils down to the architecture. Any  tightly coupled component will be difficult to extend or modifiy. Extensibility requires encapsulation and abstraction. SOA easily understands this for software components, but application developers somehow don't see how this should apply to the database.

I used to belive that cursors were the worse thing one could do to a database, I was wrong. Cursors can be refactored. But ad-hoc SQL creates a brittle database that's nearly impossible to modify. The worst thing one can do to a database is to bypass the abstraction layer with ad-hoc SQL.

I have seen many otherwise competent IT organizations become comepltely **crippled** by the lack of database abstraction. It is simply too expensive to even identiy the SQL that directly connects to a table from hundreds of reports, SQL in millions of lines of application code, and dozens of ETL procedures.

Will Sullivan argues that a logical database abstration layer in application layer is sufficient to issolate the database. I don't believe it. An abstraction layer must be complete or the database is still coupled. Unless reports and ETL processes also go through the abstraction layer, it's still too expensive to modify the database.

Extensibility: Sprocs: 10   Ad-Hoc SQL: -10  

I disagree with Peter on one point, and I agree with Paul, however I want to put it in more layman's terms.

When dealing with the maintenance issue, having a central location for dealing with data and only data, increases the ease of maintenance.  Let's take for instance, calculated data (cola + colb / colc).  In an ad hoc situation, your calculation is created and ran by the user / application.  Each user / application could calculate the same metric differently (both (cola + colb) / colc  or the previous example).  Whereas, sprocs (or even views) help eliminate that problem.  Additionally, should the calculation be wrong, you no longer have to update the application, you only have to update the sproc.  Because remember, when you update the application, you then have to deal with redeployment of that app.

Two great examples of what this affects is SOA and reporting.

- Indeed no argue regarding the security advantage, even more with sql2005 since you can create a proc using WITH EXECUTE AS ...

- Bugfixing is a second one

- Tunability without having to reinstall the full application(layer) is also non-neglectable. Especialy because your dba can do that based on the actual performance monitoring figures he collected.

- did someone consider all the (plan)hint-stuff that a dba can apply in some worst case scenarios to get the data-system to work whilst the app-dev-team rewrites/redesigns the application(s).

I'd agree with most of the points above.  However, where Stored Procs have a huge advantage for me is in the ability to make changes relatively quickly.  If you have your SQL code embedded in the app and find something that needs to be changed/updated, you have to track it down, make the changes, compile, deploy to some environment, etc, etc.  If the SQL Code is in a stored procedure, you make the change to the stored proc, test, and propagate up the chain.  I've rarely seen a shop that can easily deploy SQL Changes within the code quickly because there are usually so many other dependencies that you have to worry about.

One other thing worth noting is that stored procs can easily mask the underlying DB Schema from the app.  If the DBA's realize that splitting one table into two or three tables would help performance, they could make this change "behind the scenes" and only change the stored procs.  The code and programs would never know there was a difference.  Yes, this isn't something to do without testing, but it doesn't require re-doing object models and all sorts of other things that seem to almost always accompany SQL in the Code base.

Agree with ALZDBA. I'm not pro either, as both have their purpose. However, that being said, most ad-hoc approaches deal with not needing heavy database use, such as small in house financial app. Along with all the disadvantages that this brings, most people that are defending this approach are either lazy programmers who think they're dbas or other way around. There is a very simple fundamental fact that most seem to disregard, T-SQL is not a programming language, its a relational language driven by an engine. This engine is very dynamic, and complex, that requires maintenance, optimization, and monitoring, in order to ensure scalability and performance. By taking the code out of the database, you are leaving DBA handicapped from controlling the data flow and ensuring uptime. And while this will work in a lot of environments this is not an enterprise approach, and designs such these require complete redesign.

I prefer stored procedures as well for the ease of "where used" searches.  In an environment with perfect or ideal change management and control it isn't as much of an issue, but in an environment with 10+ disparate source safe databases, little control on coding, applications ranging from Foxpro to VB 6 to VB.net and about 70 developers any database change always creates some type of broken issue.

While I would love to move to the ivory towers of completely controlled code, I don't have the power to spend the resources or money to change that in my organization.  The best I can do is enforce the use of stored procedures and other things that abstract the database and reduce the risk of causing issues when database changes occur.

I also agree that by abstracting the database consistently it would give DBAs a huge advantage in being able to optimize the design without forcing developer allocation every time.

My take on this is slightly different. As has been discussed at length, what one would like in a SQL Server database / front end application combination is to have the best possible set up for query optimization (scalability), security, full use of SQL's linguistic powers, and flexibility in making changes to either the database schema or application without one breaking the other. The design I find that supports those goals best is: stored procedures for all data *manipulation* but *views* for returning results. This combination gives all the advantages outlined above (abstraction / flexibility, security, performance / scalability) AND avoids the problem of closure with stored procs.

If you haven't seen the "closure problem," here it is:

select * from dbo.sproc1 inner join dbo.sproc2 on sproc1.key = sproc2.key

In a nutshell, stored procedures that simply return a result set and encapsulate a select statement cripple the otherwise highly composable language we have access to, and needlessly so.

So by all means make stored procs for all your insert, update, delete logic (which is where much of the security issue is, and where the logic tends to be complex enough to want an "abstraction layer") but please, please consider views for selects instead of stored procs.

I'm late to this blog but I had to comment because it's such an interesting debate

My dad was a building contractor who was an electrician by trade.  

I asked him once why he didn't do some of the non-electrical work, like plumbing, himself since he's handy.  His reply was something along the lines of "You don't hire electricians to do plumbing". His point being you let the expert handle his/her area of expertise. I think this translates to this very well to this debate.  

I use SP's because, frankly, I want to isolate the database's internals from anything calling it and I don't want the logic tier developers to know anything about the tables or their data. My data model changes a lot and I don't want to dig through Java code finding any references and possibly missing some.  Furthermore, I've had many situation where I had to provide a SP to a customer immediately to fix a bug. Compare that to having to generate a JAR file, stop services, drop in the JAR file, re-start services, and cross fingers.

While the referenced blogger mentions isolating logic to a module, you may be able to get away doing that with simple INSERTs, SELECTs, etc but more complex code that has to churn through multiple tables using multiple statements really should be in an SP where the entire SP can be cached as a single plan and re-used as opposed to creating multiple individual plans.

Finally, I equate this debate to Java methods; methods generally keep its variables private while the method itself is public.  You just pass in a String or int or whatever and you know you'll get back some data type.  You don't care what the method is doing, you just know you get something back in some form.  SP's, to me, are no different.