row_number()存储过程分页

在sql server 2005 的新方法ROW_NUMBER做一个查询
select row_number() over (order by ProductID) as Row,Name from Product
可以看到返回结果中每条记录都有一个唯一的表示其序列号的标志。
例如我们在分页中要获取的6到第10条记录就可以采用下面的方法
select Row,Name from
(select ROW_NUMBER() over(order by ProductID) as Row,Name from Product)
as ProductsWithRowNumbers
where Row >= 6 and Row <=10
返回相关的记录。

那我们就利用row_number() 做一个存储过程分页

carate proc [dbo].[page_tbale1]
(

@pageNo int, --当前页数值,最小为1,表示第一页
@pageSize int, --每页显示条数
@PageCount int out,--获取总页数

)
as
set nocount on --使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。--获取总页数

declare @itemscount int
select @PageCount =CEILING( (count(ID)+0.0)/@PageSize)

from Product

order by ProductID

if(@pageNo <> 1)

begin

set @itemscount=(@pageNo-1)*@pageSize+1

select Row,Name from
(select ROW_NUMBER() over(order by ProductID) as Row,Name from Product)
as ProductsWithRowNumbers
where Row >= @itemscount and Row <= @pageNo * @pageSize

end

else

begin

select Row,Name from
(select ROW_NUMBER() over(order by ProductID) as Row,Name from Product)
as ProductsWithRowNumbers
where Row >= 1 and Row <= @pageSize

end

set rowcount 0