SQL Server OFFSET 分页存储过程

SQL Server OFFSET 分页存储过程

--参数值
SET @sqlquert=N'SELECT  *
FROM    dbo.tbCOM_Job
ORDER BY Job_ID ';--这里一定得加Order By
SET @CurrentPage=2;
SET @PageSize=5;

CREATE PROC  PageTest
(
@sqlquert NVARCHAR(MAX),--查询的数据源sql
@CurrentPage INT,--当前页2
@PageSize INT --每页5条
)
AS 
BEGIN
DECLARE @Pagequery AS NVARCHAR(MAX);--最终查询sql
SET @Pagequery = N'SELECT * FROM
('+@sqlquert+'
OFFSET (' + CONVERT(VARCHAR(20), @PageSize) + '*('+ CONVERT(VARCHAR(20), @CurrentPage) + '-1)) ' + 'ROW FETCH NEXT '+ CONVERT(VARCHAR(20), @PageSize) + ' ROWS ONLY
) PageDate';
PRINT @Pagequery;
EXECUTE(@Pagequery);
END;

• 普通sql

--参数
DECLARE @sqlquert AS NVARCHAR(MAX);--查询的数据源sql
DECLARE @CurrentPage AS INT;--当前页2
DECLARE @PageSize AS INT;--每页5条
DECLARE @Pagequery AS NVARCHAR(MAX);--最终查询sql

--第一种
SELECT *
FROM dbo.T
ORDER BY ID --这里一定得加Order By
OFFSET (@PageSize * (@CurrentPage - 1)) ROW FETCH NEXT @PageSize ROWS ONLY;

--第二种
SET @sqlquert=N'SELECT  *
FROM    dbo.tbCOM_Job
ORDER BY Job_ID ';--这里一定得加Order By
SET @CurrentPage=2;
SET @PageSize=5;
SET @Pagequery = N'SELECT * FROM
('+@sqlquert+'
OFFSET (' + CONVERT(VARCHAR(20), @PageSize) + '*('+ CONVERT(VARCHAR(20), @CurrentPage) + '-1)) ' + 'ROW FETCH NEXT '+ CONVERT(VARCHAR(20), @PageSize) + ' ROWS ONLY
) PageDate';
PRINT @Pagequery;
EXECUTE(@Pagequery);