0

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.

Improve this question
5
  • Better than formatting these queries and stuffing them back into your source code I would recommend converting it to a stored procedure. This allows much better formatting and you can more easily separate the layers of your application. Commented Jul 28, 2015 at 18:42
  • Thanks good point @Sean. You're very right to say use SP when they are available.I've got to take my Access hat off and think more SQL Server again! Commented Jul 28, 2015 at 18:48
  • @HarveyFrench you can use dpriver for formatting sql queries. Commented Jul 31, 2015 at 15:12
  • @learn Thansk for letting me know about this. It's very very good. Easy to use and quite powerful. Not quite as powerful as www.sqlinform.com for my purposes. They both have their strengths. (eg I could find a means of getting it to add aa a, " at he start of each line.... it came close. It's a shame you can't clone formatting options). Commented Jul 31, 2015 at 15:44
  • You can simulate stored procedures in this context (MS Access) by storing them in a table. With a Memo field, you can use any formatting you like. Press CTRL+F2 to open the Zoom box and paste in the formatted SQL. Commented Aug 14, 2015 at 1:26

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This looks like a lot of maintenance if the SQL ever needs to be modified.
Not really. Just debug print the created sql. Edit it, then use the tool to reformat it for use in vba.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.