AspNetPager 的存储过程

AspNetPager 的存储过程

ALTER   PROCEDURE  P_GetOrderNumber
AS
     select   count (orderid)  from  orders;
     RETURN

ALTER   procedure  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
     RETURN

ALTER   PROCEDURE  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