Code Generation with Stored Procedures?

I very recently posted about using SubSonic to generate my DAL and SSMS Tool Pack to generate the complementing stored procedures.  In response to the post, Jon Galloway asked a great question in the comments:

Thanks for the pointer to SSMS Tool Packs. It looks really interesting. One thing I’m having trouble picturing is the overlap between the two [SubSonic and SSMS Tool Pack]. Once you have a DAL, what do you need CRUD routines for? Bulk operations, or something that’s a result of the existing architecture?

For those familiar with SubSonic you can appreciate Jon’s question.  Since SubSonic’s generated DAL provides you with CRUD methods and easily allows you build resulting parameterized queries, you are no longer dependent upon having compiled routines sitting in the database. 

Almost on queue, Caffeinated Coder recently a great article on why one should “Just Say No to Manual CRUD” which provide a list of resources which present good counter arguments against the conventional stored proc wisdom.

So, why the heck am I still using stored procedures?

If I were to be honest, the number one reason I’m sticking with SPs is they are familiar and they provide me with a sense of comfort.  Most of my uneasiness with “embedding” data access into the application code is tied to deployment and maintenance.  For example, I like to have multiple “outs” when it comes to rollbacks and I like to keep emergency fixes as isolated as possible. Since stored procedures could be considered more atomic than even the most lightweight DLL, I can update a live application by altering a single stored procedure with more confidence than copying/replacing application file(s.)  Assuming you have a single database and a web application running on multiple web servers, in my opinion, the stored procedure update is best solution since time to implement is low, risk is minimal and downtime is eliminated. 

I had a good follow up conversation with Jon about all of this last week and the bottom-line is that one needs to put the right architecture in place based on their coding, deployment and maintenance needs.  I am currently sticking with stored procedures but this approach isn’t necessarily right for everyone.