>One other point - generating SQL code inside of the application really hurts the chance of reusability in other applications hitting the same database.

>By placing the code in a stored proc, the level of difficulty in exposing this as an API to multiple applications does down.

It all depends, First off all, you've got to identify whether this database is going to be used by different applications (from different vendors). If it is a database provided by your company, it is not in your interest to provide this functionality through SPs.

It also is a point where it access the database, directly or another tier or layer which provides the API.

Third, I don't think that the excercise we did is so difficult that it would save a lot of effort or avoids a lot of duplication when this was done seperately if neccesary. As you can see in my example (which is far more generic) it does not take a lot of code and is very simple. It would become a different story where you've got an SP that is doing complex things in a few hundred or thousand lines and you want to share that functionality. Yes then a SP is a good choice (we've got a few examples of those).