如何将 SQL 字符串格式化为格式良好且更易于阅读的可读代码块

2021-12-12 00:00:00 vba excel ms-access sql sql-server

当我编写构建动态 SQL 语句的代码时，我经常使用诸如 MS Access 提供的查询构建器工具来生成语句的基础，然后我将在我的代码中使用该工具.

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.

但是，如果此 SQL 语句格式正确，则阅读代码会容易得多.任何人都可以就如何最好地将本质上是无格式的长 SQL 语句并将其转换为更易于阅读的漂亮文本字符串或漂亮代码块提供建议.

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.

推荐答案

我以前用过这种技术，这是最好的方法吗?

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

它的关键是:

A.使用下面的aa"程序让阅读变得更容易.

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

B.如果您使用查询构建器(如在 MS Access 中)构建 SQL 字符串，最好配置一个实用程序来获取构建的 SQL 并重新格式化它，以便生成的 SQL 看起来非常像下面的那样.(我使用 www.sqlinform.com 但还有其他的，或者您可以手动完成)

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.通过在每行之前添加 vbCrLf，当 SQL 字符串内置在 VBA 中时，生成的字符串可以输出到即时窗口，易于阅读，因为它将具有线制动器并很好地排列.(这不是火箭科学)

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, "  ;"

注意:

  1. 表格连接的括号对齐

  1. the brackets for the table joins line up

我使用大写别名来强调它们是别名，以便它们脱颖而出

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

有时我会添加空格来分隔关键区域(SELECT、WHERE、GROUP BY、FROM 等)，通常是当它沿着一小段代码并且可能非常动态时(​​即当它有很多变量时改变它的构建方式)

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 )

在 SELECT、ORDER BY 和 GROUP BY 子句中，我倾向于将列之间所需的逗号放在同一行的列名前面.

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.

我不喜欢数据库表的名称中有类型，即 strFullName.然而，这不是我的架构！

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

哈维

相关文章