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).

  • 3
    You can create massively bungled, dynamic SQL functions anywhere. It doesn't matter if it's in the mainline code or if it's in a separate file. That having been said, I'm interested to see the other pros / cons around using a separate file.
    – user53019
    Dec 21, 2012 at 14:58
  • What database platform are you using @Brett?
    – CokoBWare
    Dec 21, 2012 at 17:25
  • @CokoBWare - Teradata. Why? Dec 21, 2012 at 19:57

4 Answers 4


If your problem is "impossible to understand" SQL, focus on that problem. Can not imagine that SQL with attributes does anything to make it easier to understand.

Instead it will create a maintenance burden on parsing attributes etc on an already complex language like sql. Sooner or later your extensions will prevent you from using some nice database feature. How much training is needed to teach new employees/contractors your version of sql? Extra files create a more complicated deployment process and possible support problems. How do you know the version of the file that is in use or if it has been altered by the customer or your own support to solve another issue? Everything can be solved of course but only you knows if it is worth it.

Spend your time setting up strict guidelines for writing readable/maintainable SQL first. Once that is in place and everyone agrees on the rules you can move to the next problem.

What is readable and maintainable sql varies but here is a subset of our rules. (For sql-server)

  • you must be able to easily copy/paste code to/from database and run it I.e. No string concatenations of variables in code. Use parameters or string.Replace where parameters are not allowed in sql.
  • one column per line pefixed with table/alias
  • explicit AS
  • ansi joins
  • descriptive aliases (not t1, t2 etc)
  • use CTE/CROSS APPLY to reduce complexity
  • use parameters with descriptive name for both variables and "magic" numbers.
  • This is my thinking as well. I did suggest we improve our code review process, but developers are always resistant to anything that dosen't involve more code. They want to automate the problem away and I don't think it will work. I'm not seeing many strong pro's coming out of this question, so I think I'll push harder. Dec 27, 2012 at 2:02
  • Although I was looking for pro's vs cons, I'll give this answer the winning vote as it reflects what I believe to be the proper solution to our problem. Dec 28, 2012 at 20:15

I ran into this same dilemma at my job a while back. I got the same advice you did, and here's what I thought about it:


  • SQL queries can be tested on their own, separate from the mainline code.
  • Your queries can be modified without having to recompile.


  • SQL is code. I find it easier to manage when all the code that does something is in the same place.
  • I've found it rare that you modify a SQL query without a corresponding mainline code change.

I decided that the cons outweighed the pros and left the SQL in the mainline source. It's been about a year since I made that decision and I haven't regretted it once.

  • 1
    The same place doesn't necessarily mean the same file. It could be in a separate file with the same name but a different extension, just like many IDE's separate the visual form definition from the file containing the code to execute the form's behavior. Dec 21, 2012 at 18:35

If you said the architects wanted you to extract the SQL and put it in stored procedures inside the database, I'd have said "Give them boys a cookie!". But switching from SQL assembled via string construction to SQL assembled via templating is too minor a step to deserve a reward.

  • To be clear, we are not talking about stored procedures. The proposal is to move to text files on the file system. Dec 22, 2012 at 19:19
  • 1
    I guess I wasn't clear enough, but my point is that such a move is nearly worthless, but getting SQL out of code into something like stored procs is highly worthwhile. Dec 22, 2012 at 22:48
  • Ah, sorry I see what you were saying. No, in our case we are not fans of SP's unless it serves a specific purpose. I won't bother getting into why, it's just our approach. Dec 27, 2012 at 1:56

One BIG Pro.

You can support multiple DB's simply swapping out the SQL files. So you have multiple sets of SQL Code for the different DB you want to support.

  • That is a very big plus. In our case, we're almost 100% gauranteed not to ever change. Still a valid argument however. Dec 21, 2012 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.