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
