| 版本 | 支援語法 | 範例 |
|---|---|---|
| SQL Server 2000 | **不支援** `TOP (@var)`,需動態 SQL 拼接 | `EXEC('SELECT TOP ' + @n + ' * FROM T')` |
| SQL Server 2005+ | **支援** `TOP (@var)` 括號寫法 | `SELECT TOP (@n) * FROM T` |
DECLARE @TopN INT = 10;
DECLARE @Sql NVARCHAR(MAX);
-- 注意:變數需轉字串拼接,防止注入建議用 sp_executesql + 參數化 (但 TOP 不支援參數)
SET @Sql = N'SELECT TOP (' + CAST(@TopN AS NVARCHAR(10)) + N') * FROM dbo.mtrcLanguages;';
EXEC sp_executesql @Sql; -- 或 EXEC (@Sql)
DECLARE @TopN INT = 10;
SELECT TOP (@TopN) * FROM Common.tblCode; -- 括號必要
由於 `TOP` 變數限制,分頁常用 `ROW_NUMBER()` (2005+) 或 `OFFSET/FETCH` (2012+)。
DECLARE @PageSize INT = 20, @PageIndex INT = 3; -- 第 3 頁
WITH CTE AS (
SELECT
ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RowNum,
*
FROM dbo.Articles
WHERE Status = 1
)
SELECT * FROM CTE
WHERE RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
ORDER BY RowNum;
DECLARE @PageSize INT = 20, @PageIndex INT = 3;
SELECT TOP @PageSize * FROM (
SELECT TOP (@PageSize * @PageIndex) * FROM (
SELECT TOP 2147483647 * FROM dbo.Articles WHERE Status = 1 ORDER BY CreateDate DESC
) AS T1 ORDER BY CreateDate ASC
) AS T2 ORDER BY CreateDate DESC;