ROW_NUMBER() 真分页 存储过程

if exists(select * from sysobjects where name='proc_pages')
 drop Proc proc_pages
create Proc proc_pages
@tablename varchar(100),
@pagesize int,
@pageindex int,
@orderby varchar(100),
@selectext varchar(100)
AS
declare @sqltext varchar(500)
set @sqltext='WITH NewUsers AS
(
    SELECT '+@selectext+',
    ROW_NUMBER() OVER (ORDER BY '+@orderby+') AS RowNumber
    FROM Users
)
SELECT '+@selectext+'
FROM NewUsers
WHERE RowNumber BETWEEN '+CAST((@pageindex-1)*@pagesize+1 as varchar(100))+' AND ' +CAST(@pagesize*@pageindex as varchar(100))
exec(@sqltext)

exec proc_pages @tablename='Users',@pagesize=5,@pageindex=1,@orderby='id',@selectext='*'