MS SQL Server 分页存储过程

以前经常被人家问 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