记很是很久以前收藏的,效果不错经测试 200W 下 4s 以内就能得到结果,现在拿出来与大家分享!--------------------------------------------------------------------------------------- -- -- 标题:通用分页存储过程 -- 日期:2006-02-29 -- --------------------------------------------------------------------------------------- IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE Name = 'XCommonPager' AND Type = 'P') DROP PROC XCommonPager GO CREATE PROCEDURE XCommonPager ( @fPageIndex INT, -- 当前的页页 @fRowCount INT, -- 数据的总页数 @fPageSize INT, -- 每页显示的记录数 @fPrimaryKey VARCHAR(50), -- 主键 @fColumnName VARCHAR(50), -- 要显示的列名 @fSoureName VARCHAR(50) -- 要分页显示的表或视图 ) AS SET NOCOUNT ON SET @fColumnName = 'A.' + @fColumnName SET @fColumnName = REPLACE(@fColumnName, ',', ',A.') DECLARE @commandSql VARCHAR(8000) DECLARE @totalPages INT DECLARE @currentPageSize INT --当前页的记录数 IF @fPageSize <= 0 OR @fPageIndex <= 0 BEGIN RAISERROR ('传入的页码或每页记录数大小参数错误!', 16, 1) RETURN END --如果是第一页,直接处理 IF(@fPageIndex=1) BEGIN SET @commandSql = ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) ORDER BY A.' + @fPrimaryKey + ' DESC' END ELSE BEGIN --计算数据的总页数 SET @totalPages = CASE WHEN @fRowCount = @fPageSize * @fPageIndex THEN @fRowCount / @fPageSize ELSE @fRowCount/@fPageSize + 1 end IF(@fPageIndex>@totalPages -1) BEGIN --最后一页 SET @currentPageSize = @fRowCount - @fPageIndex * @fPageSize WHILE(@currentPageSize <=0) BEGIN SET @currentPageSize = @fPageSize + @currentPageSize END SET @commandSql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@currentPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) ' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( ' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@currentPageSize) + ' ' + @fPrimaryKey + '' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] ORDER BY ' + @fPrimaryKey + ' ASc) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC' END ELSE BEGIN IF(@fPageIndex<=@totalPages/2) BEGIN --前半部分 SET @commandSql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) ' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( ' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' C.' + @fPrimaryKey + ' FROM (' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize * @fPageIndex) + ' ' + @fPrimaryKey + ' ' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] ORDER BY ' + @fPrimaryKey + ' DESC) AS C ORDER BY C.' + @fPrimaryKey + ' ASC) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC' END ELSE BEGIN SET @commandSql = ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) ' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( ' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fRowCount - @fPageSize * (@fPageIndex -1) ) + ' A.' + @fPrimaryKey + '' SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] AS A ORDER BY A.' + @fPrimaryKey + ' ASc) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC' END END END EXEC (@commandSql) GO --示例 --DECLARE @RowCount INT --SELECT @RowCount = COUNT(1) FROM XSampleTable --EXEC XCommonPager --@fPageIndex = 1, --@fRowCount = @RowCount, --@fPageSize = 100, --@fPrimaryKey = 'Id', --@fColumnName = '*', --@fSoureName = XSampleTable