Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what's going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.
Stored Procedures don't provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic 'errors' returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?
Stored Procedures can't pass objects. So, if you're not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic "bad call" error. Oracle can't tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.
Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.
When an application is made by a software house (take note!) adhering to CMMI L5 SP choices (why are other not focusing on User-Defined Functions? it is really most taxing to study another language again in the RDBMS) are debated just on the Business Analysis Phase from among other choices. Designs, backward compatibility, languages of choice, technology, RDBMS among others. So any blames will be passed on the Business Analysts and not on the developers.
When an error is incurred within the embedded code of vb or java or c and SQL, the error can not be located since a programming language are not made to check line per line in the SQL statement embedded with it. The enterprise environment of SQLServer can do the same and save the SP/UDF without syntax error. SP/UDF requires specific error handling that may be even longer than the SQL code itself. (document everything)
Codes are not supposed to be rushed, they should be thoroughly tested and there should be separate team just to test the codes. Specific error testing codes should be inserted in between evaluation to prevent unlikely errors. SP/UDF are another language distinct and separate from vb or java or or .Net. SP/UDF receives parameters and passes return values. (document everything)
For me SP/UDF/UDT/Trigger is the only way to go since programming language takes long time to tame and if another problem with SQL adds to it, it will double or even triple the team's development time by more than twice.