高效的通用分页存储过程

### 高效的通用分页存储过程解析 #### 一、概述 本文将详细介绍一个用于SQL Server 2005的高效通用分页存储过程。该存储过程由Curllion Zhang于2010年1月11日创建，旨在为SQL Server 2005数据库提供一个灵活且高效的分页查询机制。该存储过程能够根据传入的参数动态生成SQL查询语句，并返回指定页面的数据记录。 #### 二、存储过程详解 ##### 1. 参数说明 - **@tblName**：字符串类型，表示要查询的表名或多个表的连接。 - **@fldName**：字符串类型，默认值为'*'，表示要查询的字段列表。 - **@pageSize**：整型，表示每页显示的记录个数。 - **@page**：整型，表示要显示哪一页的记录。 - **@pageCount**：整型输出参数，默认值为1，表示查询结果分页后的总页数。 - **@Counts**：整型输出参数，默认值为1，表示查询到的记录总数。 - **@strCondition**：字符串类型，默认值为空字符串，表示查询条件（不包括"WHERE"关键字）。 - **@strSort**：字符串类型，表示排序字段。 ##### 2. 功能实现 - **获取总条数**：根据传入的`@tblName`和`@strCondition`动态构建SQL查询语句，统计出满足条件的记录总数并赋值给`@Counts`。 - **计算总页数**：根据`@Counts`和`@pageSize`计算出总页数，并赋值给`@pageCount`。 - **校验页码**：如果传入的`@page`值大于总页数，则将其设置为总页数；如果小于1，则设置为1。 - **生成分页查询语句**：基于上述信息动态生成分页查询语句，使用`ROW_NUMBER()`函数进行行号排序，然后通过限制行号范围来实现分页效果。 - **执行查询**：使用`sp_executesql`执行动态生成的分页查询语句。 #### 三、代码解读 ```sql CREATE PROCEDURE [dbo].[paging] @tblName nvarchar(4000), @fldName nvarchar(4000) = '*', @pageSize int, @page int, @pageCount int = 1 OUTPUT, @Counts int = 1 OUTPUT, @strCondition nvarchar(2000) = '', @strSort nvarchar(2000) AS BEGIN Declare @strTmp nvarchar(4000) -- 获取总条数 if @strCondition = '' set @strTmp = ' select @Counts = count(*) from ' + @tblName else set @strTmp = ' select @Counts = count(*) from ' + @tblName + ' where ' + @strCondition exec sp_executesql @strTmp, N'@Counts int out ', @Counts out -- 计算总页数 if @Counts <= @pageSize set @pageCount = 1 else begin if @Counts % @pageSize = 0 set @pageCount = (@Counts / @pageSize) else set @pageCount = (@Counts / @pageSize) + 1 end -- 校验页码 if @page > @pageCount set @page = @pageCount if @page < 1 set @page = 1 -- 生成分页查询语句 if @strCondition = '' begin set @strTmp = 'select * from (' + 'select row_number() over (order by ' + @strSort + ') as RowNum ,' + @fldName + ' from ' + @tblName + ') a where a.RowNum > ' + cast((@page -1) * @pageSize as varchar(128)) + ' and a.RowNum <= ' + cast(@page * @pageSize as varchar(128)) end else begin set @strTmp = 'select * from (' + 'select row_number() over (order by ' + @strSort + ') as RowNum ,' + @fldName + ' from ' + @tblName + ' where ' + @strCondition + ') a where a.RowNum > ' + cast((@page -1) * @pageSize as varchar(128)) + ' and a.RowNum <= ' + cast(@page * @pageSize as varchar(128)) end exec sp_executesql @strTmp END ``` #### 四、适用场景与注意事项 - **适用场景**：此存储过程特别适用于Web应用程序中的数据分页展示，能够有效提高用户交互体验。 - **注意事项**： - 此存储过程仅适用于SQL Server 2005及以上版本。 - 当查询条件非常复杂时，可能会影响查询性能。 - 在使用动态SQL时，需要注意SQL注入的安全问题。 #### 五、总结 该存储过程提供了一个简单而强大的工具，可用于任何需要在SQL Server 2005中实现数据分页的应用场景。通过灵活的参数配置和高效的实现方式，大大简化了开发人员的工作，并提高了系统的响应速度。对于需要处理大量数据的应用来说，这是一个非常实用的功能组件。