HI Tony,
You most certainly don't want to create SQL queries "by hand",
concatenating strings. I have worked a full year with a codebase
that's been doing this kind of stuff for >15 years and it's a
nightmare, introducing all kinds of side-effect and turning a simple
application into an unmaintainable monster.
As others suggested, there's plenty of libraries out there that
provide higher level abstractions for what you want to do, either
exposing some form of relational algebra, ORM, parameterized queries
and what not.
Then depending on the technology you use, you can focus on unit
testing the business logic, eg. that some business rule produces some
object/structure representing faithfully the needed query or data, and
have "integration" tests that really check your business logic works
with your database. Note that "integration testing" with a different
DB than the one(s) you intend to use in production is also a path
fraught with perils as any single SQL engine is different in subtle
ways from every other.
I strongly agree with this over the long term!
If we only need to format a WHERE clause from a List of expressions, then it suffices to write that code ourselves. Once we need to format a third or fourth kind of SQL Expression, I would gladly invest a few days in finding a suitable library to do that for us. I would look specifically for a library that lets us check SQL Expression objects as values without needing to execute the query on a database.
--
J. B. (Joe) Rainsberger :: ?:: ::
--
J. B. (Joe) Rainsberger :: :: ::
Teaching evolutionary design and TDD since 2002
J. B. Rainsberger