Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question
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. –  GlenH7 Dec 21 '12 at 14:58
    
What database platform are you using @Brett? –  CokoBWare Dec 21 '12 at 17:25
    
@CokoBWare - Teradata. Why? –  Brett Emerson Dec 21 '12 at 19:57
add comment

4 Answers

up vote 4 down vote accepted

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.
share|improve this answer
    
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. –  Brett Emerson Dec 27 '12 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. –  Brett Emerson Dec 28 '12 at 20:15
add comment

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:

Pros:

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

Cons:

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

share|improve this answer
    
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. –  Marjan Venema Dec 21 '12 at 18:35
add comment

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.

share|improve this answer
    
That is a very big plus. In our case, we're almost 100% gauranteed not to ever change. Still a valid argument however. –  Brett Emerson Dec 21 '12 at 19:58
add comment

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.

share|improve this answer
    
To be clear, we are not talking about stored procedures. The proposal is to move to text files on the file system. –  Brett Emerson Dec 22 '12 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. –  Ross Patterson Dec 22 '12 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. –  Brett Emerson Dec 27 '12 at 1:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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