分页的存储过程:
ALTER proc [dbo].[GetPagedProducts]
(
@pageNo int, --当前页数值,最小为,表示第一页
@pageSize int--每页显示条数
)
as
declare @itemscount int
if(@pageNo <> 1)
begin
set @itemscount=(@pageNo-1)*@pageSize+1
select Row,ProductID,ProductName,UnitPrice,SupplierID from
(select ROW_NUMBER() over(order by ProductID) as Row,ProductID,ProductName,UnitPrice,SupplierID from Products)
as productsWithRowNumbers
where Row >= @itemscount and Row <= @pageNo * @pageSize
end
else
begin
select Row,ProductID,ProductName,UnitPrice,SupplierID from
(select ROW_NUMBER() over(order by ProductID) as Row,ProductID,ProductName,UnitPrice,SupplierID from Products)
as productsWithRowNumbers
where Row >= 1 and Row <= @pageSize
end