2000: 首先获得所有的记录集合的存储过程: create PROCEDURE [dbo].[P_GetOrderNumber] 分页的存储过程 create procedure [dbo].[P_GetPagedOrders2000] create [dbo].[P_GetPagedOrders2005] SELECT orderid,orderdate,customerid,companyName,employeeName
AS
select count(orderid) from orders;—-orders为表
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
2005:
(@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)
FROM orderlist
WHERE Row between @startIndex and @startIndex+@pageSize-1
end
sql server 2000和 sql server 2005实现分页的方式_数据库技巧
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com 特别注意:本站所有转载文章言论不代表本站观点! 本站所提供的图片等素材,版权归原作者所有,如需使用,请与原作者联系。未经允许不得转载:IDC资讯中心 » sql server 2000和 sql server 2005实现分页的方式_数据库技巧
相关推荐
-      sql语句中的判断功能的使用方法
-      sql语句中的判断功能的使用方法
-      SQL语言中去掉小数点有效数字后面的所有0
-      ASP连接各种数据库的代码
-      在sql语句中实现md5功能
-      给access数据库减肥
-      asp连接access数据库代码(2)
-      更改 SQL Server 登录模式