Dynamic SQL: Yea or Nay?

You may also like...

1 Response

  1. Buu Nguyen says:

    Nice post, Chuck! I am in no particular side and see pros and cons of both approaches. You have articulated excellently examples of situations when stored proc can be useful, but on the other hand, these things should be considered as well:

    – Stored procs reduce reusability of data logic if it is to be shared among different database systems. I used to work on a project in which we had to maintain the data logic separately for SQL Server and Oracle just because stored procs were used. If we were to do differently, dynamic SQL (or a framework like Hibernate) might be a better idea.

    – Regarding testing, from the unit testing perspective, I think it is easier to compile the tests and run the whole thing if those tests are in the binaries instead of in form of stored procedures. This is even more beneficial with the integration between unit test tools like JUnit/NUnit with CI build system and code coverage tools.

    – Unless you put a lot of processing logic into the stored proc, then usually when the data logic change, the application layer will be affected in one way or another. In your scenario of the name field, I think a good design would not rely on the application logic, instead of the stored procedure, to determine the display form of the data. In other words, first name and last name fields are to be retrieved separately and stored in some objects or dataset – in that case, if middle name is introduced, the code has to be changed anyway. On the other hand, if such logic is to be put into the stored procs, the application logic is scattered among different layers and becomes difficult to test (#2), and much less portable (see #1). (And maybe the domain-driven design mindset makes me biased on this, but when I code stored procs, I don’t use those syntax that I consider more application than data logic like if/else, concatenation and so on.)

    – Finally, it is easier to stay in one environment to do the development work instead of switching back and forth between various environments (or if there’s an DBA, having to come to him/her to request him/her to change even the most simplest things).

    Of course, the point here is not to use anything over another, but about using the right tool for the right situation. For example if there is no constraint of multiple databases, the team is very comfortable with stored procs, you have excellent DBAs etc. then using stored procs can be a good idea.