SQL Server实现分页的方式

SQL Server实现分页的方式2007-05-31 动态网站制作指南 2000:

首先获得所有的记录集合的存储过程:

create PROCEDURE [dbo].[P_GetOrderNumber]
AS
select count（orderid） from orders;----orders为表
RETURN

分页的存储过程

create procedure [dbo].[P_GetPagedOrders2000]
（@startIndex int, ---开始页数
@pageSize int----每一页显示的数目
）
as
set nocount on
declare @indextable table（id int identity（1,1）,nid int） ----定义一个表变量
declare @PageUpperBound int
set @PageUpperBound=@startIndex+@pagesize-1
set rowcount @PageUpperBound
insert into @indextable（nid） select orderid from orders order by orderid desc
select O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+" "+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID
inner join @indextable t on
O.orderid=t.nid
where t.id between @startIndex and @PageUpperBound order by t.id ----实现分页的关键
set nocount off

2005:

create [dbo].[P_GetPagedOrders2005]
（@startIndex INT,
@pageSize INT
）
AS
begin
WITH orderList AS （
SELECT ROW_NUMBER（） OVER （ORDER BY O.orderid DESC）AS Row, O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+" "+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID）

SELECT orderid,orderdate,customerid,companyName,employeeName
FROM orderlist
WHERE Row between @startIndex and @startIndex+@pageSize-1
end