SQLServer 2005中的Row_Number()分页

典型的语句如下
SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY XXX) AS RowNo
FROM tbl
) AS A
WHERE RowNo >= 11 and RowNo <= 20; 




--分页存储过程
create procedure [dbo].[proc_TestPage]
--表名
@tablename nvarchar(255),
--排序字段
@sortcolumn  nvarchar(255),
--每页记录数
@pagecount  int,
--页号
@pageindex  int
as
declare @beginrow int
declare @endrow int

set @beginrow=0
set @endrow=0

set @beginrow=(@pageindex-1)*@pagecount+1
set @endrow=@pageindex*@pagecount

declare @sqlstr nvarchar(4000)

set @sqlstr='with table1 as(select *,ROW_NUMBER() OVER(ORDER BY '+ @sortcolumn+' ) AS ROW'+
            ' FROM '+@tablename+') '
set @sqlstr=@sqlstr +' SELECT * FROM TABLE1 WHERE ROW BETWEEN '+cast(@beginrow as nvarchar )+' and '+cast(@endrow as nvarchar)

exec(@sqlstr)

 

文章来源：http://topic.csdn.net/u/20090409/14/d94695f7-9010-4b27-9587-29dcebb51a4d.html