最近在研究SQL Server的数据库分页存储过程。提供一个通用的数据库分页存储过程供大家学习探讨,如果您有更好的方法或者您想咨询请发送邮件到邮箱:jinguo504@163.com
/*-------Microsoft SQL Server 2000 Pagination-------*/CREATE PROCEDURE MsSql2KPagination( @Columns NVARCHAR(500), --数据库中相关表表列 @TbNames NVARCHAR(200), --数据库中相关表表名 @WhereCondition NVARCHAR(1500), --Where条件语句,不含有Where关键字 @OrderColumns NVARCHAR(350), --排序列名称,支持多列排序,例如ORDER BY column1,column2但是语句中不能还有ORDER BY关键字 @IsOrderByASC BIT, --排序方式1:ASC,0:DESC @KeyWord NVARCHAR(100), --很关键,可以是上述数据库表列的一个列名,主要用于按某一列排序,建议开始使用时是主键列名 @CurrentPageIndex INT, --当前分页页面数,如果程序是第一次使用则该值为1 @PageSize INT, --程序需求每页显示的数据条数 @TotalPages INT OUTPUT, --数据库中总的页面数量 @TotalRecords INT OUTPUT --数据库中总的记录数量)ASSET NOCOUNT ON--------设置WHERE条件--------BEGINDECLARE @WHERE NVARCHAR(2000)DECLARE @OTHERWHERE NVARCHAR(2000)IF ISNULL(@WhereCondition,'') = '' BEGIN SET @WHERE = '' SET @OTHERWHERE = ' WHERE ' ENDELSE BEGIN SET @WHERE = ' WHERE ' + @WhereCondition SET @OTHERWHERE = ' WHERE ' + @WhereCondition + ' AND ' ENDEND--------设置ORDER BY条件--------BEGINDECLARE @ORDERBY NVARCHAR(800)IF ISNULL(@OrderColumns,'') = '' SET @ORDERBY = ''ELSE BEGIN IF @IsOrderByASC = 1 SET @ORDERBY = ' ORDER BY ' + @OrderColumns + ' ASC ' ELSE SET @ORDERBY = ' ORDER BY ' + @OrderColumns + ' DESC ' ENDEND--------计算分页页面数量和总数据条数--------BEGINDECLARE @_PageNum NVARCHAR(1000)SET @_PageNum = 'SELECT @TotalRecords=COUNT(*), @TotalPages=CEILING((COUNT(*)+0.0/' + CAST(@PageSize AS VARCHAR) + ') FROM ' + @TbNames + @WHEREEXEC SP_EXECUTESQL @_PageNum,N'@TotalRecords INT OUTPUT,@TotalPages INT OUTPUT', @TotalRecords OUTPUT,@TotalPages OUTPUTEND--------现在开始实行分页--------BEGINDECLARE @_PAGESQL NVARCHAR(5000)
IF @CurrentPageIndex = 1 SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns + ' FROM ' + @TbNames + @WHERE + @ORDERBYELSE BEGIN IF @IsOrderByASC = 1 SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns + ' FROM ' + @TbNames + @OTHERWHERE + @KeyWord + '>' + '(SELECT MAX(' + @KeyWord + ') FROM (SELECT TOP ' + STR(@PageSize*(@CurrentPageIndex-1)) + ' ' + @KeyWord + ' FROM ' + @TbNames + @WHERE + @ORDERBY + '))' + @ORDERBY ELSE SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns + ' FROM ' + @TbNames + @OTHERWHERE + @KeyWord + '<' + '(SELECT MIN(' + @KeyWord + ') FROM (SELECT TOP ' + STR(@PageSize*(@CurrentPageIndex-1)) + ' ' + @KeyWord + ' FROM ' + @TbNames + @WHERE + @ORDERBY + '))' + @ORDERBY END EXEC(@_PAGESQL)END GO
以上存储过程适用于sql server 2000和2005,效率一般般。在SQL Server 2005中已经提供了一些新新的函数可以实现快速分页。您可以点击如下地址进入 SQL Server 2005 通用分页存储过程