如下的SQL 代碼中，如果在 #1 或者 #2 中添加了 ';'，會出現錯誤。

--根據頁碼獲取測試記錄集

CREATE PROCEDURE dbo.sp_ATE_getTestRecordByPage

@pageIndex INT = 1 --頁碼數（從1 開始）

AS

--獲取每頁的記錄數

DECLARE @recordCount INT;

SELECT @recordCount = CAST([value] AS INT) FROM dbo.tbl_ATE_config WHERE [name] = N'recordCountInEachPage';

SET @recordCount = ISNULL(@recordCount,30);

--建立CTE

WITH CTE_allTestRecord

AS (

SELECT ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS sequenceNO,

[ID],

CONVERT(VARCHAR(16),[testDateTime],120) AS testDateTime,

[lineCount],[totalTestCount],[totalPassCount],

dbo.fun_ATE_getPercent([totalPassCount],[totalTestCount])AS passPercent,--合格率

[result],[caption],[PINO],[partNO],

dbo.fun_ATE_formatSequent([beginSN_letter],[beginSN_number],[beginSN_numberLen]) + N'~ ' + --起始流水號

dbo.fun_ATE_formatSequent([beginSN_letter],[beginSN_number]+[totalTestCount]-1,[beginSN_numberLen]) AS sequenceID, --終止流水號

[testModel_name],[comment]

FROM dbo.tbl_ATE_testRecord --#1

) --#2

--提取分頁的記錄數

SELECT * FROM CTE_allTestRecord

WHERE [sequenceNO] BETWEEN (@pageIndex-1)*@recordCount+1 AND @pageIndex*@recordCount;

RETURN

GO

ITPub博客

CTE 內部的逗號使用問題