> This code has to be maintained, versioned, etc.
Specifically to a Microsoft stack this specific pain point is outright negated, in fact, Microsoft's answer (Visual Studio SQLPROJ/Data Dude) exposes migrations for the horrible metahistory hack that they are. SQL projects are stored in source control as though they were C++/C#/what-have-you. Just like the rest of our code, the 347kloc SQL portion is handled on by CI and the migration (as well as the creation) scripts are generated for us - based on the schema differences (that it determines for us) between our last release and the current release.
So far as SQL Server goes, maintenance and versioning woes are an outright myth.
More pros:
4. A policy of using SPROCs only is a good way to significantly reduce the risk of SQL-related vulnerabilities. If developers are required to justify why they are sending raw queries to SQL they will have a hard time introducing e.g. an injection vulnerability.
5. A lot of the overhead of query execution (parsing, resolving objects, etc.) is done when you CREATE or ALTER the procedure, this can results in a performance benefit (especially if the application is chatty).
More cons:
5. Query plans for SPROCs are aggressively cached, in extremely rare circumstances this can play havoc with performance (direct contradiction of my own pro 5).
6. Triggers+family are invisible logic. They can cause confusion during debugging.