Sorry, but I disagree completely with this author.
I have written a web-based system that builds all pages from metadata. Every table in every system is searchable and editable through the same TWO pages. Lets look at the benefits of my approach:
Currently, I have about 150 different tables through the different systems.
How many update/insert/delete stored procedures would I need for all those tables? A lot.
Every column on every table is searchable/sortable includinging multiple filters being able to be applied. I have ONE codebase for all that code. It includes role and user based table and column level permissions definable as metadata.
"Advantage 1" of stored procedures - speed
The author admits this is not much of an advantage, and so far I have found no performance issues in my design.
"Advantage 2" of stored procedures - injection attacks.
Yep, definitely a concern, but I've coded for that and since every system uses the same codebase, I never have to code for it again.
"Advantage 3" security - not giving permissions to base tables
This just doesn't compute for me. You'd have to make all your views indexed, then you'd have to have separate code to look at the TABLE when doing inserts and the views when doing selects. If you are doing your inserts and deletes in a stored procedure anyway, I see no "advantage" here and if you never give an individual user permission to the database in the first place, there shouldn't be a concern anyway.
I have another page in my system which generates a crosstab type report where the user can change the selection for each axes to look at information in different ways. The queries generated by my ASP.NET pages are huge, but the performance is amazing. NO WAY are you doing that with stored procedures, at least not in a timely manner from just metadata. To make a new crosstab report in a new system takes me about 5 minutes. To make a new SYSTEM with say 5 tables in it and have edittable and searchable ROLE base permissions pages takes about 30 minutes.
I think the whole Dynamic Sql is bad movement is very mistaken - perhaps is it driven by the minority if DBA's dealing with GB sized tables or by the certification "mandatory" book smart group. Frankly, I don't know.
To them, I say - trying writing and supporting a system a month with one developer/DBA all in stored procedures. Then a year in, go add some features, perhaps new sorting or filtering capabilities. Then see which way you wished you had your system written.
Non-Certified 8 Year user/DBA of SQL Server