SQL Server 2005通用分页存储过程及多表联接应用

这是不久前写的一个分页存储过程，可应用于SQL Server 2005上面： 代码如下: if object_ID(‘[proc_SelectForPager]’) is not null Drop Procedure [proc_SelectForPager] Go Create Proc proc_SelectForPager ( @Sql varchar(max) , @Order varchar(4000) , @CurrentPage int , @PageSize int, @TotalCount int output ) As /*Andy 2012-2-28 */ Decla 《SQL Server 2005通用分页存储过程及多表联接应用》 在数据库管理系统中，分页查询是常见的数据检索方式，特别是在大型应用系统中，由于数据量庞大，一次性加载所有数据可能导致资源浪费和用户体验下降。SQL Server 2005提供了一种高效的方式来实现分页查询，即通过存储过程来实现。本文将详细介绍一个适用于SQL Server 2005的通用分页存储过程，并探讨其与多表联接的应用。 让我们看看这个分页存储过程的代码： ```sql if object_ID(‘[proc_SelectForPager]’) is not null Drop Procedure [proc_SelectForPager] Go Create Proc proc_SelectForPager ( @Sql varchar(max), @Order varchar(4000), @CurrentPage int, @PageSize int, @TotalCount int output ) As /*Andy 2012-2-28 */ Declare @Exec_sql nvarchar(max) Set @Exec_sql=’Set @TotalCount=(Select Count(1) From (‘+@Sql+’) As a)’ Exec sp_executesql @Exec_sql,N’@TotalCount int output’,@TotalCount output Set @Order=isnull(‘ Order by ‘+nullif(@Order,”),’ Order By getdate()’) if @CurrentPage=1 Set @Exec_sql=’ ;With CTE_Exec As ( ‘+@Sql+’ ) Select Top(@pagesize) *,row_number() Over(‘+@Order+’) As r From CTE_Exec Order By r ‘ Else Set @Exec_sql=’ ;With CTE_Exec As ( Select *,row_number() Over(‘+@Order+’) As r From (‘+@Sql+’) As a ) Select * From CTE_Exec Where r Between (@CurrentPage-1)*@pagesize+1 And @CurrentPage*@pagesize Order By r ‘ Exec sp_executesql @Exec_sql,N’@CurrentPage int,@PageSize int’,@CurrentPage,@PageSize Go ``` 此存储过程接收五个参数： 1. `@Sql`：用户提供的基础查询语句，用于获取数据。 2. `@Order`：排序条件，可以为空，如果不指定则默认按时间排序。 3. `@CurrentPage`：当前页码。 4. `@PageSize`：每页显示的记录数。 5. `@TotalCount`：输出参数，返回总记录数。 存储过程首先计算总记录数，然后根据当前页码和每页大小创建一个带有行号的Common Table Expression（CTE）。对于第一页，直接从CTE中选择前`@PageSize`条记录；对于其他页，使用`BETWEEN`操作符来选取正确的页范围。这种设计巧妙地避免了重复的子查询，提高了效率。 在实际应用中，这个存储过程可以处理单表查询和多表联接的情况。例如： 1. **单表查询**： ```sql Exec proc_SelectForPager @Sql = ‘Select * from contacts a where a.ContactType=1’, @Order = ”, @CurrentPage = 3, @PageSize = 20, @TotalCount = 0 ``` 2. **多表联接**： ```sql Exec proc_SelectForPager @Sql = ‘Select a.Staff,a.OU,b.FName+b.LName as Name from staffOUHIST a inner join Staff b on b.ID=a.Staff and a.ExpiryDate=”30001231” ’, @Order = ”, @CurrentPage = 3, @PageSize = 20, @TotalCount = 0 ``` 值得注意的是，存储过程中的`@Sql`参数不允许直接使用CTE，而是应该将整个查询逻辑封装在`@Sql`中。此外，由于使用了`sp_executesql`动态执行SQL，这增加了SQL注入的风险，因此在实际使用时需确保输入参数的安全性。 在SQL Server中，分页查询有多种实现方式，如使用`TOP`和`OFFSET`、`ROW_NUMBER()`等。本存储过程使用`ROW_NUMBER()`结合CTE，既简洁又高效，尤其适用于处理复杂的多表联接查询。然而，对于非常大的数据集，可能需要考虑其他的优化策略，如索引优化、数据分区等。 总结来说，SQL Server 2005的这个通用分页存储过程为开发者提供了灵活、高效的分页查询手段，无论是在单表查询还是多表联接的情况下，都能轻松应对。在实际开发中，可以根据项目需求进行适当调整，以实现最佳性能和用户体验。