Where SQL statements are executed from a program, what is the best practice to store the statement text?

Most often it is in a read only string, in the source file it is executed in.

I think a better approach would be to have a separate text file containing all SQL statements, that are then read in to a dictionary object where the key is the purpose of that statement. Statements can then be executed by using their key, decoupling database implementation from the program and allowing reuse of statements between program objects.

Is this efficient? Is there a better way? Thank you!

  • 1
    That approach is debatable. Reading from a text file is more costly then from a table.
    – Maxood
    Commented Nov 27, 2012 at 16:32
  • If you don't want the code in the program put it in stored procedures. There is enormous debate in industry over whether stored procedures or in-code are better, I land strongly on the stored-procedures side myself (as well as do most engineers these days that I've run into) but that is again still something that has been heavily debated, and the large use of ORMs these days to some makes it moot and to others is used as an argument against stored procedures. You'll have to sift through a great deal of opinion and tales of whoa on both sides to find which side you're on. Commented Nov 27, 2012 at 16:35
  • 1
    Well, stored procedures are pretty much going to preclude any sort of dynamic SQL building, @JimmyHoffa. My gripe with stored procedures is that the development environment for them (at least in SQL Server) is just terrible. The performance and security benefits are undisputable, but the differences are smaller than most SP advocates suggest. Commented Nov 27, 2012 at 16:45
  • 1
    @MichaelT: A data access layer still executes SQL; you've merely pushed the complexity somewhere else, though I will grant you that executing external SQL statements must be justified by a specific need. Commented Nov 27, 2012 at 16:48
  • 2
    @RobertHarvey its pushed into a library that is unit tested. It can be reused (the OP's reason for having them on a file system). Be it raw sql, stored procedure invocations, or an ORM, I believe it should be extracted from the application to promote consistent reuse. Runtime loading of the statements gives me the shivers.
    – user40980
    Commented Nov 27, 2012 at 17:13

2 Answers 2


The only reason I can think of to treat SQL as data instead of code is if you are dynamically generating SQL statements, and you don't need to store those.

There are actually advantages to going in the other direction; by hard-coding your SQL statements into Stored Procedures, you benefit from certain security and performance optimizations that the RDBMS can provide.

SQL is already naturally "parameterized;" if you need additional flexibility beyond that, you should carefully balance your application requirements against the additional security challenges that will accrue from treating your SQL statements as data. SQL injection is one example of a potential security leak that can occur.

If you need generalized CRUD (create, read, update, delete) statements for each of your tables, you should use an Object-Relational Mapper to generate them.

  • SQL injection can occur even if you use you use stored procs. Injection prevention is about parameterizing your arguments. It has nothing to do with sql as data or code. I can give examples of injection through stored procs called in a non-parameterized way but it won't look good in a comment.
    – mike30
    Commented Nov 27, 2012 at 21:04
  • @mike: Creating SQL statements dynamically using string concatenation is how most people inadvertently produce SQL injection vulnerabilities, and it's easy to get that wrong. Having such vulnerabilities in sprocs is much less common. Commented Nov 27, 2012 at 21:07
  • It's just as easy to inject with stored procs as it is with inline or dynamic sql. Consider this: cmd.CommandText="exec Myproc " + userInput; That's a glaring injection hole. Of course it's ridiculous to have un-parametrized arguments to your proc, but you could say the same thing when dealing with dynamic or inline sql.
    – mike30
    Commented Nov 27, 2012 at 21:17

I'd say the best way is to use Stored Procedures, if the DBMS supports it, for the following reasons:

  • They centralize the logics so you don't need to keep multiple copies in sync, which seems to be what you cares most.
  • They centralize the permission control (authorization) to the DBMS.
  • They are pre-compiled so they run faster.
  • (minor) The string size sent over the network is smaller (code and DBMS usually run on different servers).
  • +1 for mention of network traffic, that had never occurred to me
    – Daniel
    Commented Nov 28, 2012 at 8:34

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.