SQL Server 2000和 SQL Server 2005实现分页的方式

2000:

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

createPROCEDURE [dbo].[P_GetOrderNumber]ASselect count(orderid) from orders;----orders为表RETURN

分页的存储过程

create; procedure [dbo].[P_GetPagedOrders2000](@startIndex int,; ---开始页数@pageSize int----每一页显示的数目)asset nocount ondeclare @indextable table(id int identity(1,1),nid int); ----定义一个表变量declare @PageUpperBound intset @PageUpperBound=@startIndex+@pagesize-1set rowcount @PageUpperBoundinsert into @indextable(nid) select orderid from orders order by orderid descselect O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName from orders Oleft outer join Customers Con O.CustomerID=C.CustomerIDleft outer join Employees Eon O.EmployeeID=E.EmployeeIDinner join @indextable t on O.orderid=t.nidwhere t.id between @startIndex and @PageUpperBound order by t.id;----实现分页的关键set nocount off

2005:

create; [dbo].[P_GetPagedOrders2005](@startIndex INT, @pageSize INT)ASbeginWITH 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 Oleft outer join Customers Con O.CustomerID=C.CustomerIDleft outer join Employees Eon O.EmployeeID=E.EmployeeID)

SELECT orderid,orderdate,customerid,companyName,employeeNameFROM orderlistWHERE Row between @startIndex and @startIndex+@pageSize-1end