I'm in a position where we've got some brittle code that constructs SQL-like queries via text concatenation with parameters for inputs. The data source that it queries is fast and scalable but lacking tool support. Over time, addition entities and properties have been added to the data source that need changing or have obsoleted others, so the queries need changing.
I can see that this will happen again in a few months and then again.
In order to reduce errors introduced into the text queries, I suggested writing the queries into a separate e.g. .SQL file and then running some kind of code generator tool that could get the schema from the data source and generate a code wrapper around the SQL-like query which was easy to re-generate at any time and would give compile errors for any out-of-date client code.
This idea was met with some skepticism and resistance, even when I offered to fund the development myself.
What are the reasons against doing this? and, for balance, the reasons to go ahead and do it?
(I already saw this post with a couple of answers, but its' not comprehensive)