Our Architecture team is proposing a framework that would see our SQL queries moved from coded strings within our applications, into a file based system where we would invoke them with function calls. Our application makes heavy use of SQL queries ranging from the mundane, to very complex. This is a .NET solution.
The idea is that each query would be written to text files, and we would be able to tag fields, joins, and conditions with attributes. When calling our SQL functions, we could pass arguments to toggle these attributes on/off based on our business logic, allowing for only the portions of the SQL we need to be executed.
Although I like the idea of abstracting away our SQL, I am skeptical that this system will result in any tangible benefits. The major driver of this is our past experience where many coders created some massively bungled dynamic SQL functions which were impossible to understand. The idea is that this will make it easier to define your queries, will make all queries more easily testable, and clearly define the business logic involved. I am unsure if this will survive the test of reality.
I’d like to get some feedback on the pros/cons of this approach.
Also, before anyone suggests it, the Entity Framework was rejected due to questionable support from our database provider (Teradata).