Some of these issues can be addressed using TSQLMacro, a project I created a while ago:

http://www.datamanipulation.net/tsqlmacro

One specific group of problems I thought of is centralization of commonly-used bits of code. For instance, in one project I worked on, every one of the 350+ stored procedures in the system had four or five nearly identical predicates in the WHERE clause for row-level security. At one point we had to change it a bit, and we had to change every single procedure. Had we had macros, we could have centralized the code. One change in one place is a good thing...