编写含WITH语句的SQL查询时报Incorrect syntax near '.'错误如何解决

问题原因 #

报错的核心原因是SELECT查询的列之间缺少分隔逗号，属于基础语法错误：
你在最后SELECT语句中，S.NotificationSent所在行的末尾没有加逗号，和下一行的S.RowNumber + S.RowNumberInverse - 1 AS TotalRows列定义直接连在一起，SQL解析器无法正常识别别名S的字段调用，就会抛出「Incorrect syntax near '.'」的报错。

修正后的代码 #

declare 
@FromDate DATETIME = '2021-09-01 12:44:56.493',
@ToDate DATETIME = '2021-09-01 12:44:56.493',
@StartRow INT=1,
@EndRow INT=5;

WITH SWIFTMessages AS (
Select M.DocumentId, 
M.FileName, 
M.DateAdded, 
M.NotificationSent, 
M.DateNotificationSent,
ROW_NUMBER() OVER (ORDER BY M.[DateAdded] ASC) AS RowNumber
from  [Swift].[MessageLog] AS M
Where ((@FromDate IS NULL OR @FromDate <= CAST(M.DateAdded AS DATE))
AND (@ToDate IS NULL OR @ToDate >= CAST(M.DateAdded AS DATE)))
), SWIFTMessageLogs AS (
SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY RowNumber DESC) AS RowNumberInverse
FROM SWIFTMessages
)

SELECT S.DocumentId, S.FileName, S.DateAdded, S.NotificationSent,  -- 此处补加逗号即可
S.RowNumber + S.RowNumberInverse - 1 AS TotalRows
FROM SWIFTMessageLogs AS S
WHERE 
S.RowNumber >= @StartRow
AND S.RowNumber <= @EndRow
ORDER BY S.RowNumber

GO

内容的提问来源于stack exchange，提问作者user7336033