2000:

首先獲得所有的記錄集合的存儲(chǔ)過程:

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

分頁(yè)的存儲(chǔ)過程

create; procedure [dbo].[P_GetPagedOrders2000](@startIndex int,; ---開始頁(yè)數(shù)@pageSize int----每一頁(yè)顯示的數(shù)目)asset nocount ondeclare @indextable table(id int identity(1,1),nid int); ----定義一個(gè)表變量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;----實(shí)現(xiàn)分頁(yè)的關(guān)鍵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