If you use server-side code, then it becomes possible to do the DRY thing by factoring common elements out into sprocs and UDFs that can be reused by other sprocs and UDFs.

If your procedures are already in the database then it's a lot easier to go through and systematically evaluate how all the queries are performing as a maintenance task (to check for missing or unused indexes, for example).

Sprocs are a heck of a lot more testable than DSQL. Unit testing suites for SQL are generally designed to work with sprocs.

Some folks complain that sprocs tightly couple you to your DBMS. Personally I've found that switching DBMSes is rare. If you're doing it with any frequency, that's probably a symptom of deeper problems. And if you're using anything more advanced than what was in the SQL '92 standard (I sincerely hope you are - a lot of good stuff has happened to most RDBMSes in the past 20 years) then tight coupling to the database server is inevitable. On the other hand, what's comparatively much more common is schema changes. And tight coupling of your application to the database schema is easy to avoid with the help of stored procedures.