SQL Server使用ROW_NUMBER函数进行分页
01
ALTER PROCEDURE PROC_PAGEING
02
03
@qCols varchar(4000), -- 查询列
04
@qTables varchar(4000), -- 查询表
05
@qWhere varchar(1000), -- 条件
06
@oKey varchar(100), -- 排序
07
@pageSize int, -- 大小
08
@pageNumber int = 1 --, 0 页码
09
AS
10
set nocount on
11
BEGIN
13
DECLARE @sqlstr AS varchar(8000)
14
DECLARE @sqlTable AS varchar(8000)
15
16
set @pageNumber = @pageNumber - 1;
17
set @sqltable='SELECT ROW_NUMBER() OVER(ORDER BY '+ @oKey + ' ) AS RowId,'+ @qCols + '
18
FROM '+@qTables + ' where '+ @qWhere ;
19
set @sqlstr='SELECT * FROM ( '+@sqlTable+' ) AS D
20
WHERE RowId between '+ str(@pageNumber*@pagesize +1)+ ' AND '+ str((@pageNumber +1)*@pagesize) ;
21
exec (@sqlstr);
22
END