How to format a SQL string into a readable block of code that is well formatted and more easy to read

When I am writing code that builds a dynamic SQL statement I often use a query builder tool such as that provided by MS Access to generate the basis of the statement that I will then use in my code.

However, it is so much easier to read the code if this SQL statement is well formatted. Can anyone offer advise about how best to take what is essentially a long unformatted SQL statement and turn it into a nice text string or a nice block of code that is easier to read.

Answer

I have used this technique in the past, is this the best method of doing this?

The Key to it is:

A. using the following “aa” procedure which make reading so much easier.

B. If you build the SQL string using a query builder (like in MS Access), it is good to configure a utility to take the built SQL and reformat it so that the resulting SQL looks pretty much exactly like that below. (I use www.sqlinform.com but there are others, or you can do it manually)

C. By adding vbCrLf beofre each line, when the SQL string is built in VBA the resulting string can be output to the immediate window an easily read as it will have line brakes and line up nicely. (It’s not rocket science)

Public Sub aa(ByRef a As String, ByVal b As String)
    ' This is deliberately not a function, although it behaves a bit like one
    a = a & vbCrLf & b

End Sub


' The function is called in code like this:

Dim a as string 

a = ""
aa a, "    SELECT CUR.txtLevel      AS [Current]  "
aa a, "         , NLPMi.strFullName AS [Full Name]  "
aa a, "         , NLPMi.DOB         AS [Birthday]  "
aa a, "         , NLPMi.Surname     AS [Surname Name]  "
aa a, "         , TOOLS.txtWCMTool "

aa a, "      FROM ( ( ( tblPeopleWCMSKILLSByYear AS SKILLS"
aa a, "                 LEFT JOIN tblSkillLevels AS CUR  "
aa a, "                        ON SKILLS.bytCurrentID = CUR.atnSkillLevelID
aa a, "               ) "
aa a, "              INNER JOIN [qrylstNames-LPMi] AS NLPMi  "
aa a, "                      ON SKILLS.intPeopleID = NLPMi.atnPeopleRecID
aa a, "             )"
aa a, "            INNER JOIN tblWCMTools AS TOOLS "
aa a, "                    ON SKILLS.intWCMSkillID = TOOLS.atnWCMToolID"
aa a, "           ) "

aa a, "     WHERE ( ( (SKILLS.bytYearID) = YEAR(DATE())-2012 )  "
aa a, "           AND CUR.txtLevel   >= " & MyVariable1 & "  "
aa a, "           AND TOOLS.txtWCMTool = '" & MyVariable2 & "'"
aa a, "           )  "

aa a, "  ORDER BY NLPMi.strFullName"
aa a, "        ", " & MyVariable3 & ""

aa a, "  ;"

Note that:

1. the brackets for the table joins line up

2. I use uppercase aliases to re-inforce they are aliases and so that they stand out

3. Sometimes I add spaces to separate the key areas (SELECT, WHERE, GROUP BY, FROM etc), usually when it is along bit of code and possible is very dynamic (ie when it has a lot of variables that change how it is built )

4. In the SELECT, ORDER BY and GROUP BY clauses, I favour putting the commas that are needed between columns in front of the column name on the same line.

5. I don’t like database tables have the type in their name ie strFullName. However, this is not my schema!

Harvey