There is another holy war converging about the SP vs. Parameterized SQL  topic. I got into one not long ago, and I wouldn't get into this one if there wasn't some new stuff brewing in it.

Eric Wise started this with a The Pragmatic Adhoc SQL vs Stored Procedures Discussion, Jeremy D. Miller responded with Why I do not use Stored Procedures and then Eric posted a new post, this time with In Response To No Stored Procedures which has some new questions that I have not seen before.

1.       About the security, naturally, security is a multi layered approach. In the database, I would grant permissions to views or tables according to the user's need. Granting a SELECT permissions does not equals to letting the user SA access to the database, after all. I also tend to add another layer in the application level, which is completely transparent to my code and my business objects, you can see a high level overview here. Then there is the application logic itself.

2.  Transactional  Database Structures : A good coverage of the topic can be found in Roy 's What payroll system are a pain to design – Temporal data  and in Eric's "Transactional" Database Structures.  I am currently working on a big payroll system which is using NHibernate, and this issue is a major PITA. The problem is that both the relations between objects and the objects themselves are changed as time goes by.  We solved this issue by deciding that each object is the system is composed of Id, which cannot be changed, and a history, which contains immutable version of the data for a specific date. A highly simplified version of this will look like this:

This way, I can ask any object for a view of how it was in a point in time. The views also handle the associations, so if I ask an employee to see its view in 2000-12-31, I can easily get any data that I need without dealing with the complex timed model. For instance, if I want to check if the employee had a  car in a certain date, I can use this code:

This model translate very well into something NHibernate can handle. It reduce a lot of the problem that you have when you deal with such system, since  the infrastructure code remove a lot of the  problems that you get in such models. To set a new property, I ask the employee to give me its current status, change whatever I want, and then I give it back and saves. A new record is inserted to the database, and the employee's current state moves to the newest piece of data.

3.       OR/M in complex databases structures. The system above gets its schema from a main frame 30 years old or more, written by a bunch of guys that thought that transliteration Hebrew in English is fun (with spelling mistakes!). Enough said, I think.

4.       Stored Procedures makes TDD slower. The problem with SP and  TDD is that you don't get any seam to verify the expected behavior. I may have  a procedure that takes a long time to run, and I need to call it from another procedure that eventually calls that procedure, I'm in more than a bit of a bind. I can replace the long running procedure on the fly, and spend the rest of my life running from angry DBAs, but that about all I can do. There is no way to put mocks in the database. And database access takes time. Often a lot of time.

Note about security: If an attacker is in the position where he can run arbitrary code in your system, you are screwed no matter what. OR/M, ADO.Net or SP, he can make the appropriate calls to do whatever he wants.

Oh, and unrelated tidbit, in the last six months I wrote over 600 stored procedures (data warehousing project), so I feel confident that I know my stored procedures.