Are we still talking about Stored Procedures vs. Dynamic SQL?(转)

Rob Howard and Frans Bouma still are. And I guess, I am now too. Let's summarize a few of the facts from these counter points:
    *Any form of pre-compilation or cached query plan arguments are moot betweem SQL and Procs. Rob has some outdated information and Frans corrects that in his post.
    *Stored Procedures can offer the perf benefits if they are designed properly that Rob claims by avoiding round trips and unncessarily data transfer when trying to get computed or aggregated data out of the database.
    *Both are susceptible to SQL Injection attacks if the SQL is concatenated with parm values.

Let's talk about security. Frans thinks that Role Based security is the way to get fine grained security in your database while using embedded or dynamic SQL. Frans's solution of adding users and roles in the database is a dated technique back to client server 2 tier systems. Web-based or other wise distributed applications typically have a connection pool - and unless you are going to have a connection pool for each role, then you can't rely on SQL Server based role based security to be your cop. Frans goes on to talk about how views can be used to encapsulate security rules just like a stored procedure.

Both Frans and Rob talk about the brittleness of SQL with regards to schema changes. Rob thinks your SQL centralization/encapsulation  should occurr inside of stored procedures. Frans think you should do this in a data access component that is part of your application. Frans hasn't really explained what his application's component does specifically but it sounds like he prefers to dynamically create the SQL on the fly by reflecting on schema of entities in his application.

What both of them has avoided is any realization that talking to a SQL Server database is the same problem as talking to any external service. Whose responsibility is it to provide the encapsulation and deep understanding of the underlying database schema. The answer to that question can't be answer universally. Back in May 2005, I blogged about the notion of DatabaseAsService.

Is your database a shared service between several applications? Some folks might even go as far as to say that their database is an enterprise service. Especially in this case it makes perfect sense to encapsulate complex internal schematics inside of the single shared resource the database. This can be done with Stored Procedures or Views, but do you really want each application to have intimate knowledge of deep schema details? That's brittle way beyond the scope of a single application.

In other cases, your database is more like a file that your application persists its data and it is not a shared resource. In these cases, the database is not really a service in terms of Service Oriented Architecture principles. In fact, I'd go as far to argue in these cases that the db is such an intimate part of your application's design that there should be no “mapping“ of schema inside/outside of the database and that they could/should be the same. Go ahead and make the full set of tables/schema public to your application logic.