Serious question: Why is having stored procedures generated at design time better than having dynamic SQL generated at run time? It's not like there has been a meaningful performance benefit for the last several years.
No, performance isn't really a reason to go with Stored Procedures.
It's all about the readability, maintainability and debugability of your code. If you have everything generated in simple classes backed by simple stored procedures, you can look at a stack trace and immediately see what's going on without having to mentally unravel what you think your ORM might have been doing at that point. That is, you'll be looking at a single line of code that does a single thing, rather than a null reference in a hashtable of hashtables that was loaded at runtime from an XML file.
It's also a lot less duplication of effort. Realistically, the SQL calls that your app makes for CRUD actions will only ever change when you change your schema. With that in mind, it seems a bit wasteful to re-generate that same SQL over and over again with every page request. In my mind I'd rather build it all once after a schema change, and drop it into source control where it's part of the record of what your code is doing.
I'd also like to hear a reasonable answer to that question.
For me, the only advantages of stored Procs over straight SQL (generate or hand coded) are:
* Security - you can revoke rights to directly access / change the tables from the app layer, making all access be through procs. However, in general you can probably create a similar security setup using views.
* Many SQL calls that need to be run as a set to give a single answer - this avoids the round trip latency of firing many SQL calls from the application.
I'd am genuinely interested to hear of more advantages.
