SQL Server OFFSET 分页存储过程
2020-06-14 本文已影响0人
YANG_LIVE
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);
效果图