MS SQL Server 分页存储过程

hc6538

　　以前经常被人家问 SQL server 如何实现分页？当一张表里面有数十万条或者更多的数据时，该怎么办？如果数据库是 SQL Server 2000，我会回答：TOP，使用 TOP 嵌套查询（或者临时表），如果是 SQL Server 2005/2008，我会回答：使用 WITH，ROW_NUMBER 和 OVER。

SQL Top
　　SQL Top，返回结果集中的前 N 条记录。下面的例子是返回订单量最多的前十位客户的信息。另外 TOP 关键字还可以根据百分比来返回结果，但是这里不作讨论。
　　


SELECT TOP 10 * FROM Customers -- 这是一个最简单的例子
--下面是返回订单量最大的 10 位客户的信息
SELECT TOP 10 Customers.CustomerID, Customers.CompanyName, COUNT(*) OrderCount
FROM Customers INNER JOIN Orders ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY OrderCount DESC

　　这种方法很容易就得到了前 10 条记录，但是如果你想得到第 11 到 20 条数据的时候，又该怎么办呢？答案是：临时表。
　　

　　

-- 取出前 30 条数据到临时表 #TEMP 中
SELECT TOP 30 * INTO
#TEMP
FROM Customers
ORDER BY CompanyName ASC
-- 再从临时表中取出前 10 条数据到另外一个临时表 #TEMP2 中
SELECT TOP 10 *
INTO #TEMP2
FROM #Temp
ORDER BY CompanyName DESC
-- 最后从 #TEMP2 中得到想要的数据
SELECT * FROM #TEMP2

　　
　　使用这种方法，如果仅仅是想得到前几页内容的时候，还算方便，但是如果用户想要一页一页连续不断得到数据的话，这种方法效率就太低了。

WITH，ROW_NUMBER 和 OVER
　　这是 SQL Server 2005 中新增的方法。下面的这个例子是返回结果集中的第 20 到 29 条记录。
　　


WITH Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (ORDER BY CompanyName) AS RowNumber
FROM Customers )
SELECT *
FROM Cust
WHERE RowNumber BETWEEN 20 AND 30

　　SQL Server 2005 中的 WITH 关键字是为结果集指定一个临时名称，类似旧版本 SQL Server 中的临时表的概念。但是，这里最起作用的是 ROW_NUMBER 和 OVER 关键字，它们为每一条记录指定了一个行号。这种方法对于数据量大的表，查询速度非常快，在超过 250000 条记录的表上测试，表现仍然良好。

集成到存储过程中
　　为了以后使用方便，何不把它们集成到一个存储过程中呢？而且这个存储过程中，要可以灵活的自定义分页大小 @PageSize，和页数 @PageNumber，以便用户可以想看哪页就看哪页。
　　


CREATE PROC GetCustomersByPage
--页大小，第几页
@PageSize INT,
@PageNumber INT
AS
DECLARE @RowStart INT
DECLARE @RowEnd INT
IF @PageNumber > 0
BEGIN
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber + 1
SET @RowEnd = @RowStart + @PageSize - 1
WITH Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (ORDER BY CompanyName) AS RowNumber
FROM Customers )
SELECT *
FROM Cust
WHERE RowNumber >= @RowStart AND RowNumber <= @RowEnd
END
　　

　　此存储过程有两个参数，@PageSize 和 @PageNumber，使用方法如下：
　　

　　

EXEC GetCustomersByPage 10, 1

　　自己修改后存储过程
　　

　　

USE [UILogDB]
GO
/****** Object:  StoredProcedure [dbo].[Usp_Pagination]    Script Date: 11/09/2011 09:22:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<jianxiong>
-- Create date: <2011-10-27>
-- Description:<Pagination>
-- =============================================
CREATE PROCEDURE [dbo].[Usp_Pagination]
-- Add the parameters for the stored procedure here
@table varchar(200),--表名
@field varchar(max),--字段名
@orderby varchar(200),--排序字段
@PageNumber int,--页数
@PageSize int--页面中行数
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @RowStart int
declare @RowEnd int
declare @sql varchar(max)
if @field=''
set @field='*'
if @orderby=''
SELECT top 1 @orderby=syscolumns.name FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id(''+@table+'')
if @PageNumber=''
set @PageNumber='0'
if @PageSize=''
set @PageSize='5'
IF @PageNumber > 0  
SET @PageNumber = @PageNumber -1   
SET @RowStart = @PageSize * @PageNumber + 1  
SET @RowEnd = @RowStart + @PageSize - 1

set @sql=
('
with temptable as
( select row_number() over(order by '+@orderby+') as id,'+@field+' from '+@table+' )
select * from temptable
where id >= '+convert(varchar(15),@RowStart)+' and id<= '+convert(varchar(15),@RowEnd)+'
')
exec (@sql)
END