I've made a summary post with urls to various blogs (including yours and jeremy's) so people have a good overview of what's said, in which order ;).

"I'm assuming that if you're anti - stored procedure that you are also anti-trigger."

That is not the case.  While there is a very small minority of thoses on the pro-dynamic SQL side that say that no stored procedures or anything that ties you into a specific database should be used, they are  the very small minority.
The major of thoses that have problems with stored procedures in only with thier use in CRUD based access.  We have no problems with views, triggers, constraints or even used stored procedures used to solve non-CRUD problems

Point #1 is the absolute killer of "No Sp's is good" as far as I'm concerned.
In Enterprise development, Security Is Paramount.  In every place I've worked (in a career spanning 15 years of windows development), giving the application account, or the user accounts, permission to perform data updates on tables is unthinkable.  Naturally, this precludes the use of any Dynamic SQL and anything but SP's to perform updates.

Like you, I've also wondered why the OR mapper world seems to ignore this - even DLINQ doesn't support SP's (and isn't going to) and that isn't out yet.

I'm also "familiar" with TDD and, again, I agree with you that there is no difference between mocking a resultset from a query or a SP - as long as they conform to your required "schema", there is no issue......

Looks like we'll just have to agree to disagree - and carry on using our SPs.

Dave

Frans Bouma summarizes a war that has been brewing since the popularity of ORMapping and Code Generation....

A single point to add...regarding Security

Eric, you are assuming that you can't secure the Database without sproc which is totally untrue.  You can secure tables even columns within the database, so you could for instance grant select to everyone in a particular group (or to the website user for instance).  While this type of security scares me a little, I do understand their position (It's not for me though)

BTW, reading both this post and Jeremy's was nice to finally understand why someone would use an O/R Mapper, because my original understanding was that it was a time saver.  My personal experience playing with them is that you really don't save any time... Hibernate requires you to build XML to map the DB to the object model which to me seemed to take about as long as actually building a proc... some of the others provide you with a GUI to do all that work... but you're still going through the same process even though it might take a little less time. And yes, some do build objects dynamically or generate them for you, but the process requires you to go in and select what fields will be used and what functionality will be available; not a time saver at all.  However, searching is huge win for the O/R Mapper especially when it's a lot of fields...

With reguards to security, yes, you can typically secure most objects (tables, etc.) in databases, but that generic approach still leaves a big hole. In an environment where people have access to databases (in terms of connection), and companies must comply with various regulations, you need a finer grainularity of security. So, for example, you can lock a user out of writing to a table so he or she can still read from it, but you cannot lock out rows the user shouldn't be seeing (perhaps becuase they belong to customers for another auditor or rep, for example). You can generically apply security to DB objects, but you cannot apply security to custom DB operations without SPs. The generic OR mapper which builds SQL in-code assumes that only its code will use the database (and hence no need for additional security or rules on the DB side), which may be idealistic, but certainly not realistic in a large enterprise, especially one with legacy databases and heterogenus tools and apps.

Lastly, I don't buy into the main argument being responded to (from the original post) - because it makes things sound as if nothing is more important than speed of development in TDD. I thought the main piont was about reliably producing verifiable and visibly working code in small cycles. Security always adds hurdles, and that's one of those law-of-nature tradeoffs. So for the sake of security, you sometimes have to deal with a few more inconveniences. I could argue that having to log in to my workstation every day and every time I leave my desk slows my productivity, but it'd be quite irresponsible and dangerous to remove logins.

Jay: I agree about NHibernate for the most part. When I was using it there weren't really any solid 3rd party generators that I could find, so manual maintenance was a bit cumbersome.

Still, that's not the case with every mapper. XPO and LLBLGenPro are both a breeze. It's even simpler with ActiveRecord however:

require 'active_record'
ActiveRecord::Base.establish_connection(config[:database])
class Tag < ActiveRecord::Base; end
tags = Tag.find(:all)

You just established application-wide connection settings, created a model called Tag, and selected all tags out of the database. In 4 lines. The mapping is automagic.

As long as you're willing to use generated or inherited classes there's no reason you couldn't do the same thing (easily) in .NET however. It's a bit beyond me at the moment, but you could probably even modify your type at runtime with a little hackery.

"I'm curious to know what the O/R mapper world does about security?"

Let me answer with a question of my own: what does the sproc world (or even you specifically) do about security? Do your applications simply allow access to all of their functionality and bubble up any access violations from the database when the user tries to do something that the database says they can't do? I would hope not. Likewise with OR mapping, permissions can be granted at the database level to tables, views, and yes even sprocs to ensure integrity, but any robust application should have code to handle permissions in a user friendly way. That point is important: database features (such as permissions, views, relations, and constraints) are used to maintain data integrity, while business logic goes in code where it testing and maintenance are easier. Our company does software for the US Department of Defense, and we use O/R mapping for much of it. In many ways I think security is more straightforward that it was at the last place I worked where the use of stored procs was mandatory for all interaction with the database. The biggest argument our DBA used for this policy was security, but it’s only an illusion of security – security through obfuscation.

“I'm assuming that if you're anti - stored procedure that you are also anti-trigger.”

First, don’t confuse pro-O/R with anti-sproc. Like stored procs, triggers can have their uses, though much less frequently. We don’t use them at all where I work currently, but that’s a policy that was set by our DBAs (citing replication concerns), not the developers. We have written parts of a couple apps that are historical and in one case temporal. We have implemented these in the business tier and I personally think the code is much cleaner and easier to maintain than the equivalent triggers and such would have been. Not only that, but we can test this code without having to touch the database, which as Jeremy pointed out makes for much faster testing feedback cycles.

Reporting is certainly one area where sprocs are beneficial (and there are others). I have yet to see a decent reporting solution that works well with objects for much beyond simple grids. As William said, those who are completely anti-proc are a minority and probably only feel that way because they haven’t done the types of application where sprocs are useful.

Regarding jkimble’s comments on O/R mappers and code generators: we have experienced very tangible time savings by using a combination of these two tools. There was learning curve, but it more than paid off. If you were just playing around with them, you might not have seen the benefit because of the scale of what you were implementing, the tools you were using, and your lack of proficiency using them (whereas using sprocs/ADO is probably old hat to you).

Eric: Stored Procedures are not the same thing as Views. I don't know why you insist on lumping the two together as I've yet to see any posts questioning the utility of Views.

And during development you might just use a DataReader/DataWriter, but any sane deployment/development scenario should obviously include phasing those out as your model matures and you're ready to start writing integration and environment tests.

Security at the database layer is tough to figure out sometimes though. Permissioning is often a business decision and can constitute business logic, and you really don't want to put that into your data tier if you can get away with it. Where do you draw the line?

btw It's nice to see there are still forums to discuss these issues though. I'm disappointed that Jeremy has closed off comments on his posts. It's a little like putting your palms over your ears and singing.

ewise said:
I get the impression that most of the speed ORM people are doing the following:

1. Create application connection account
2. Grant account select, insert, update, delete on all tables
3. Hope that application/ui is written such that nothing bad happens.
------

I've got news for you.  That's what most SP people do too.  Obviously, that doesn't mean that's the only way or the suggested way of doing things.

I've written apps that way, but only for unimportant things where all the data is essentially public anyways.

But you can use Integrated Security and Kerberos and things like that with an ORM to pass user security tokens directly along to the database level and use DB permissions there (as long as your web platform and DB platform are integrated enough).

When people talk about using SP's to manage security, as I understand it, they are talking about SP's that have data access rights seperate from that of the end user.  This same model can be achieved by having a single-point-of-entry web service and all of the security permissions handled there.  The UI would go through this web service (or remoting or some other OO RPC) to get to the data.

The main thing is to use the right tool for the job.  I'm certainly not an advocate of ORM for everything.  SP's *do* make a lot of sense in truly enterprise scale projects with plenty of engineering resources from all disciplines.  The DB is the logical place to maintain transactional integrity, after all.  In other situations, they don't make as much sense.  

In many low-resource situations, the idea that the DBA will ever have time to look at or tune an SP is pure fiction.  In that case, it's just downright stupid to use SPs because they make it harder to maintain (divergant skillsets, source synchronization, unit testing, etc.).  When an app is harder to maintain, it will be slower and less secure because the limited amount of effort available to fix it won't go as far.

Report-heavy apps and apps that do lots of bulk operations should leverage the database functionality as much as possible.  Single-item-at-a-time apps that map clearly to OO programming (like a Forum with Threads, Posts, and Users) work very well with the ORM way of doing things.


RE: NHibernate saving time

Working with an existing database schema, an ORM may not save you much time if you're not a wiz at mapping.  They really suck if you're trying to map an existing object model that doesn't actually match the existing data model.  For instance, if some table has a column which looks like a foreign key but has no constraint at the DB-level and the table has 0.001% values that don't actually match the foreign table, you still can't map that safely as parent-child relationship in your ORM.

Where ORMs really save time is in new small-medium projects with no preexisting schema.  First, you design your object model.  Next, you write a simple DataLayer API with generic Get and SaveOrUpdate methods and you implement (hopefully reuse) a really stupid persistance layer based on serialization to TEMP and hashtables.  The rest of the application developers can begin developing against the StupidThrowawayDatalayer while you use NHibernate to generate your schema and implement a real DataLayer.

Using NHibernate (or another ORM) to generate the schema for you really saves a lot of time and makes it easy to change your schema as your Object Model changes.  If you use GUIDs for primary keys, then it's really easy to preserve all the data like test objects and lookup values that you've already entered into the system.  You just do a FindAll(), serialize everything to a file, blow away and recreate the DB with the new generated schema, deserialize the file, and do SaveOrUpdate() on everything.

Eric,

I mean absolutely no offense by this, but I think you (and everyone else that has tried to use security as a reason for using sprocs exclusively) are creating somewhat of a straw man argument with this security thing. I'm not always very good at explaining myself so I'll try again, hopefully without so much rambling as I'm prone to.

First, I have worked in 'enterprise' environments with sensitive data and subject to auditing. As I said before, I currently write systems for the DOD that consume not just sensitive, but classified information, and O/R mapping is one of the tools that we use for these systems.

Second, why do I have to pick between A and B in your scenario? Why can't it be:

C) We use this ORM mapper so we don't have to write AS much data layer code. AND The application does disallow users access to pages and controls that query sensitive data.  In addition, if they would somehow subvert this and execute code that queries sensitive data on the database, those sensitive stored procs and views are secured via active directory and run in impersonation mode so even those the user executed a valid code procedure the database would kick them out.

The point I was trying to make before was not that it was better to put security in the client or the business layer and not put it in the DB. Rather, some level of security should be in all those places. The point is you can have a secure or insecure app using either sprocs or ORM.

To use your example, if I have a Patients table with sensitive information in it and I can use either sprocs or views/ORM to view/update that data, which is more secure? The answer is that for 99% of cases, they are exactly the same. Just as I can grant execute rights to my procs to determine who can view/update what, I can do the same with tables/views. The ONLY instance in which you decrease your attack surface by using sprocs is when you need to control row-level access to a certain peice of data. I think we can probably agree that in *most* systems the need for this is restricted to a miniscule subset of the data. I have no qualms with using sprocs to handle these edge cases when the circumstances or my employer deem it necessary. It's all about using the best tool for the task at hand.

"In addition, I challenge you, ...to justify taking the control away from the DBA team and giving it to the programmers."

I would never try to justify this. All of this type of work is either done by, or goes through our DBAs, as it should be.

"To use your example, if I have a Patients table with sensitive information in it and I can use either sprocs or views/ORM to view/update that data, which is more secure? The answer is that for 99% of cases, they are exactly the same."

I disagree with this.

Take the application out of the equation - you have a hacker attempting to "fix" some data in the database.  If the user-id he has obtained (Either a user of the app, or the application's service account) has *any* update permissions on tables and the hacker gets a direct connection to the database, he can directly update the data he needs to "fix".
If he can only access the tables through stored procedures, he can only update what the stored procedures let him update.  It will also still fall under your processes, so any auditing or validation logic within the SP still applies.

For maintaining security, it's much easier to limit a database role to "executing sp's" than it is to "columns x,y and z on table a, columns e,f and g on table b......(and so on) As you'd have to do if you were using Dynamic SQL.

I agree it takes much more discipline to write applications this way, but it really is a good payback option - lots of security for a little effort.

"Take the application out of the equation... If he can only access the tables through stored procedures, he can only update what the stored procedures let him update."

And I still say that for the majority of DB interaction in most apps (including so called 'enterprise'), there's no real difference in the level of security between having stored procs or not. Say you have a Patients table and you create sprocs for each of the CRUD operations. What is the difference between "exec my_delete_proc @id" and "delete from Patients where id = @id"? Answer: none. Same for any of the other operations.

Of course someone will point out that sprocs can have custom logic or hide sensitive data. My answer is that if you use sprocs for everything, then most of them end up having little or no logic related to security (maybe 5%?). For locking down sensitive data there are other ways that work as well or better for most cases where access needs to be restricted on one or more columns; column level rights and views are two that come to mind.

And that's just for the sensitive data. One of the databases I develop against has 150-200 tables. Of those I would guess that maybe 5-10% contain the type of data that might need to limit modifying to sprocs in order to achieve maximum level of security. Much of the data in any given app isn't even near that level of sensitivity; departments, payment options, shippers, shipping options, products, suppliers, you name it. When you eliminate the boilerplate sprocs used to interact with these items, the remaining sprocs actually become a useful tool instead of a mountain of useless clutter you have to sort through when you have to do maintenance.

"For maintaining security, it's much easier to limit a database role to "executing sp's" than it is to "columns x,y and z on table a, columns e,f and g on table b......(and so on) As you'd have to do if you were using Dynamic SQL."

Again, for the majority of DB entities this is a non-issue. I've never used column-level security, and I think I'd sooner use a view or even a sproc. For ease of maintaining security, I personally think it's much easier to do on a view/table level than with sprocs. Depending on the naming convention you use, how consistently it's used in and between databases, and whether you use a tool such as Enterprise Manager or SQL scripts, managing security on multiple sprocs per entity can be almost as easy as managing SIUD permissions on a view/table or it can be much harder. I don't see how it could ever be easier, but that may come down to personal preference and what you're used to.

I'll say again that I don't have any problem using sprocs when they are the right tool for the job (mostly heavy DB lifting). But not everything is a nail, so why insist on always using a hammer?

RE: Jacob

"And I still say that for the majority of DB interaction in most apps (including so called 'enterprise'), there's no real difference in the level of security between having stored procs or not. "

Not entirely true. Please don't over generalize. Also, just because a lot of Apps are written purely with CRUD type mentality, doesn't mean that all apps are. Services, after all, in a general sense, are and should be more than simple CRUD. Perhaps the thinking moves in that direction because we're looking at from an O/R mapping perspective, which is a bit like the "if all you have is a hammer, everything looks like a nail" scenario.

"Say you have a Patients table and you create sprocs for each of the CRUD operations. What is the difference between "exec my_delete_proc @id" and "delete from Patients where id = @id"? Answer: none. Same for any of the other operations. "

There is indeed a difference. Let's say that as a managerial user, you still can only delete patients at your facility from the database. The SP can stop you from deleting a patient that belongs to another facility. Whereas with Delete rights to the entire table, you can delete anything you want. So someone may counter that there's business logic in the middle tier to prevent that, which is all well and good if everyone only uses the middle tier to access the data - something which is not realistic in a lot of environments, even if it's for legacy code/tools reasons. Also, with simple table delete security, any questionable user or rogue/monkey programmer with a little savvy can now (intentionally or inadvertantly) bypass the middle tier and directly delete any row in the table.

There are multiple layers of security involved in any n-tier or distributed system, and it's always a security concern when one layer must implicitly trust another layer. That's just about rule #1 in terms of security.

I'm a little late jumping in on this thread, but I thought I'd chime in with my experience.

In most applications we develop at our shop, we view the database as having two distinct layers or schemas.  We view the underlying table structure as a private schema, on top of which we build a public schema through views and stored procedures.  Direct table access is strictly forbidden, we force end users/cleints to use stored procedures for any updating/inserting/deleting they might want to do.  This gives us a heavy metal abstraction to the underlying schema and we've had great success being able to change the underlying schema without disruption to end clients.  Views allow clients the luxury of dynamic sql queries, but through a controlled view that composites our underlying schema into a public "view".

The argument that stored procs slow down the development cycle is absolutely retarded.  I will gladly sacrifice "speed" for "consistency" and the capability of altering my schema without killing the applications that consume it.  That being said, there are certainly concepts that are more easily executed dynamically than in a stored procedure, and in our application any instance where dynamic sql needs to happen on the client happens through a view instead of the view's underlying table structure.  Here, again, we gain the same benefit of the stored procs in the capability of altering the schema without breaking the clients.

To me, this flexibility in design outweighs any of the nitpicking about execution plans and security that seems to be the common arguments against/for stored procedure usage.  And for environments where multiple applications are accessing the same database, the exposure of a public API helps greatly in the long run versus the short term.

Ok... compared to you guy's having this discussion... I am a total amature with regards to this stuff. However, I do have some experience working in both small as well as very large (financial company ). And basically, I agree with the point that security belongs in both places. If you don't use sprocs, the DBA team will have a big headache securing everything, but that is their job.

Developers have to include security at differnt points in the application as well. That is our job.

We are jacks of all trades, so in every place i worked, developers wrote sprocs, and put them on dev servers. IT Security and DBA agree on permissiosn granted to all the objects. That way ensuring that they will work in apps and databases. If someone circumvents the app, they get stoped at the DB security level as mentioned. But most cases the security is comming from the app layer, if you arent supopsed to be somewhere, and are not so,me crazy hacker, you should not be anywhere near the DB.

Now... there is an additional thing with regards to the security aspect, since it was mentioned that application develoeprs dont put this into their sprocs.

In one organization i worked at, inside the database there is tables for permissioning things, kind of like an active directory permissioning thing, or sql permissiong model, that lets you permission tables, colums, whatever. And in almost all of our sprocs, the data  that is selected, is joined against that, so only permissioned data gets to the app. And that has nothing to do with DB's.

Anyway, Dynamic SQL some people say is easier to maintian... maybe sometimes, but what about when there is a db cahnge, now if it's dynamic... i have to recompile and distrbute my app all over again. with sprocs, i dont have to worry about anything other than updating the sprocs.

ORM is very nice, and usefull... but it is a tool for the application layer, and should not be thouht to replace the DAL or SPROC layer in my opinioon. The biggest problem with ORM is lack of support for sprocs.  Then again, even with ORM, as mentioned you almost always need to extend/modify the code to bring in security and other pieces of the applciation puzzle. ORM ( as mentioned above ) is in my mind... most useful for the small apps, or areas where you do something really straight foward and Mainly for selecting things out and showing them. Like quick binding to a grid or something.

Jon Gilkison (2 posts up) sums all this needless back and forth pretty well.

If you're developing a web app, whether it's an 'application db' or an 'integration db' is not relevant.  There isn't a single good argument for not abstracting the table structure of any reasonably sized database from the public at large for any CRUD-related activity.  PERIOD............Hence, you use views and sprocs.  

If you're working with a really small database that will likely never grow and rarely if ever change.......you can get away with not using sprocs and views.

For those with larger DB implementations and frequently changing requirements coupled with strict security and data integrity madates, there's simply no other rational option but to employ sprocs and views.

In response to what Jacob said (and adding to what Rob said):

"And I still say that for the majority of DB interaction in most apps (including so called 'enterprise'), there's no real difference in the level of security between having stored procs or not. Say you have a Patients table and you create sprocs for each of the CRUD operations. What is the difference between "exec my_delete_proc @id" and "delete from Patients where id = @id"? Answer: none. Same for any of the other operations. "

That answer is half the truth: Sure, "exec my_delete_proc @id" and "delete from Patients where id = @id" are equivalent, but "exec my_delete_proc @id" and "delete from Patients" are not.

Assuming you implement security against the Patients table by granting DELETE permission on it and you implement security on the proc by granting EXEC permission on it, then the sproc will only ever allow for a single row to be deleted, but the ad-hoc statement will allow for all rows to be deleted, and thus allow for the statement "delete from Patients" to be executed.

Security is paramount. If it makes your development work a little tougher, then so be it. Managing role-based security on tables and views is far more difficult than managing role-based security on stored procedures, and it leaves the database much more exposed.

Sure, you can hope that down the road some programmer doesn't inadvertently write code that compromises the database, or that some other development group is given access to the database and compromises it, but it is unwise for a developer to assume that he/she is the only one who touches the data, and if the database has an interface that prevents direct access to the data strctures, it is very much more likely to remain secure.

NetTiers....   stored procs plus codegen ORM.

I love ORMs, but Jon Gilkison and Shim are dead on.  Generalizable and layered security is only one checkbox on the long list of the benefits of db-level abstraction.

The company I work for now sells and supports real-time finanancial software for hundreds of clients.  It is basically a big database with about 20 applications hanging off it (with their own histories).  The problem is that it is mostly all just parameterized SQL coming from the apps.  Controlling or evolving the data logic (let alone changing the schema) becomes exponentially more difficult.  Dynamic/QP/app-SQL is a maintenance nightmare, not simply because of any possible bugginess or insecurities, but because of the restrictions it imposes on data model progress.

Each of the 20 apps is impertinent enough to pretend that it alone has the 'DAL' and is 'responsible' for determining data logic.  App writers (such as myself) tend to believe that their apps will last forever and that no other apps will exist or are important.  Hey, the data will surely outlast the app!

Perhaps if all the 20 apps had started with a common DAL - as a logical bottleneck to the db we would be in a better place... but what about next year's flavor of DAL?  And third-party applications?  Having the logical bottleneck be an abstraction at the database level could have solved many our problems.  (OK: changing db vendors means porting sps.)

Ant> "stored procs plus codegen ORM" I agree, this is my line of thinking also.  Get the best of both worlds.  With stored procs and views providing an abstraction, ORM/DALs have a much easier mapping job too, and each app could have its own ORM/DAL if it wanted.

This is a very interesting thread I must say. Here's my $0.02:

Perhaps one of the solutions to this problem is for the database table schemas to contain the rules for performing basic CRUD operations on a row-by-row or even column-by-column level. Something like the following, defined in a tool such as a SQL Server Management Studio designer or through some sort of SQL extension:

Table 'Transactions', used by multiple separate companies:

CompanyId
TransactionId
Amount
IsClassified

-- Only allow Acme's Reviewer role to select non-classified transactions from its own company
ON SELECT ALLOW * WHERE rolecontext() = 'Acme Reviewer' AND CompanyId = 1 AND IsClassified = 0

-- Only allow a totaling engine to select an Amount
ON SELECT ALLOW Amount WHERE rolecontext() = 'Acme Totaling Engine' AND CompanyId = 1

-- Only allow Acme's Manager role to update its own company's transactions, and only allow Amount and IsClassified to be updated
ON UPDATE ALLOW Amount, IsClassified WHERE rolecontext() = 'Acme Manager' AND CompanyId = 1

-- Deny deletion of CompanyId 2's rows
ON DELETE DENY WHERE CompanyId = 2

It seems like without a schema-level, granular solution such as this, the logic is already being pushed up further than it should go, whether it's to stored procedures (which in many cases act as business logic and not simply CRUD) or to an O/R mapper.

This solution would give DBAs the row- and column-level control they desire, protecting against insecure stored procedures (SQL: DELETE Transactions) or blanket ORM-generated code (Code: Transactions.DeleteAll();)

I simply don't view stored procedures and code-driven logic to be that different. We just need a more elegant security solution such as the one I presented that abstracts that concern away from ALL business logic, whether it be stored procedures, views or code.

In my experience, dynamic SQL/DAL solutions pose some ugly problems:

1. Once you have allowed systems to go into production that use dynamic SQL, it is extraordinarily hard to reign those systems back in and use something else like stored procedures.

2. Dynamic SQL allows a developer to write "Select * From BigHugeFatTable Cross Join BigHugeFatTable." Sure, such a problem *might* be found in testing that application, but when that application goes into production, where other applications are using the same database it will not be easy to diagnose. Which brings me to the next problem...

3. Diagnosing problems with rogue dynamic SQL statements is expontentially more difficult than with stored procs. Using the profiler, the DBA can determine and correct poorly performing stored procedures without having to deploy a new version of the application(s) or DAL(s). However, with dynamic SQL the best they can do is to alert someone that a query somewhere is performing poorly. Depending on whether you have a single or multiple DALs, that could be incredibly difficult to track down.

4. As far as I can tell, the dynamic SQL solution requires absolute control over access to the database. You really have to be able to choke all access through a single DAL such as through a windows or web service. In otherwords, you really have to require that everyone goes through your DAL or else you lose any control over the queries that are executed against the database. In a large enterprise, this is incredibly difficult to achieve unless you are requiring access through a service. The reason is that if the credentials used by your service get out to another development team, you have lost control over your database. There is no question that it is possible to prevent this scenario using a dynamic SQL type DAL, but I think that the dynamic SQL requires more sleepless nights to ensure that the database is protected from both outside systems and other development teams.

PingBack from http://wordpress.kineticdocs.com/2006/09/05/the-dynamic-sql-vs-stored-procedures-is-the-most-common-geek-battle-of-the-century/

In the case of large databases with billions of records and dozens of applications (different platforms and languages) accessing it I can say dynamic SQL, ORMs, and middle tier business engines just do not work. Coming from an OO background these lessons were painful. In this environment databases are King and applications must follow its rules and benefit highly from seeing the database as a SP interface. My god! SQL in each application or even in the middle tier would be insane in a multi lingual multiplatform environment. A SOA shop may be different but the huge benefits you get from an abstracted SP driven database is too great to ignore. TDD? How could you possible improve upon SPs as the basis of the unit test in an abstraction layer?

The real problem is you need a good DBA and SQL developer to take advantage of a database. Purely OO shops don’t understand this and suffer as a result. We have discovered that a good SQL developer is by far more valuable than an OO one.

Why is everyone so convinced that every developer on your team would be writing select statements against the database if you don't use SP?

In any real architecture you have a level of abstraction that accesses the database. This layer is usually created by an application DBA (someone who knows about databases and the application).  The rest of your team (who may not have any SQL/Database skills at all) write their code on top of the data access level.

There really is no reason the data access level needs to be written in stored procedures.  In fact, it seems like a huge step backwards to insist that this level would be written in SP.  There are much better tools and languages to access and manipulate databases, so why would you not use them?

> Why is everyone so convinced that every developer on your

> team would be writing select statements against the

> database if you don't use SP?

>

> In any real architecture you have a level of abstraction

> that accesses the database. This layer is usually created

> by an application DBA (someone who knows about databases

> and the application). The rest of your team (who may not

> have any SQL/Database skills at all) write their code on

> top of the data access level.

That sounds great but I find it does not work in practice. One reason is that other development teams have to know about your Data Access Layer and abide by its use. In a medium sized company or larger, it is more likely that one team will not know about the DAL (or even the existence) of another team. Second, DBAs are generally not great developers in languages such as C#. So, it is likely that the DBA does not have the skills to build said DAL. Third, your use of the word “the”, as in “the application” is a misnomer. In medium sized companies there are generally at least a half dozen or more projects in development at the same time that all work against different portions of the same database. It is simply not possible for one DBA (typical for a medium sized company but even large companies do not have more than a few) to keep up on all the database changes required for those projects. It is more likely that the teams have one or more individuals that are versed in database design creating that team’s DAL. Fourth, it is much more difficult, IMO, to choke access through a DAL than it is at the database itself. Once you go down the dark path and allow direct access to tables, forever will it dominate your destiny.  Months later you will watch as the Profiler demonstrates some other app somewhere in the company that is accessing the database directly. Fifth, stored procedures are far more resistant to change than a DAL. A DAL is going to be written in some language such as C#. Years from now, when the next language du jour comes out, you will either be faced with rewriting that DAL or dealing with whatever compatibility features are still supported.

> There really is no reason the data access level needs to be

> written in stored procedures. In fact, it seems like a huge

> step backwards to insist that this level would be written

> in SP. There are much better tools and languages to access

> and manipulate databases, so why would you not use them?

Stored procedures provide a reliable way of choking all access through a clear abstraction layer. It is substantially easier to restrict access to the database only through stored procedures than it is to require the use of some other data access layer. I’m not suggesting anything relating to better security or performance with stored procs. Rather, I am stating that stored procs make it far easier to control access to the database.

That said, if you were to abstract the DAL into a service accessed using something like XML *and* locked all access away except from that service, then it would make sense to have a DAL. However, you simply substituting one service layer for another.