sql-server - 基于参数分页(OFFSET FETCH NEXT)

Question

参数为空时如何显示所有数据，

DECLARE @PageSize INT = 5
        ,@PageNo  INT = 2

SELECT *
FROM [MyTable]
ORDER BY [NO]
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

Answer 1

你可以试试这个

SET @PageSize = ISNULL(@PageSize, 2147483647) -- max int
SET @PageNo = ISNULL(@PageNo, 1)

SELECT *
FROM [MyTable]
ORDER BY [NO]
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY