ALTER
PROCEDURE
P_GetOrderNumber
AS
select count (orderid) from orders;
RETURN
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
( @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
( @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