通用分页存储过程
在回答数据库方面的问题的时候遇到一个哥们问如何处理大数据量级的问题,我看一哥们回答的不错,尤其是这个通用的分页存储过程,先COPY过来和大家分享。
---------------------------------------------------------------------------------------
--
-- 标题:通用分页存储过程
-- 日期: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'
--SELECT 1
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'
--SELECT 2
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'
--SELECT 3
END
END
END
EXEC (@commandSql)
--
-- 标题:通用分页存储过程
-- 日期: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'
--SELECT 1
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'
--SELECT 2
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'
--SELECT 3
END
END
END
EXEC (@commandSql)
原帖子地址是: