分页的存储过程

分页的存储过程：

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