SQL Server 2005高效分页存储过程详解与优化
在数据库应用开发中，分页查询是处理海量数据列表与报表展示的核心技术。本文将深入分享一个专为SQL Server 2005设计的通用分页存储过程，该过程巧妙利用ROW_NUMBER()函数实现高效数据分片，在保证优异性能的同时，提供了极高的易用性与灵活性。
存储过程核心代码解析
以下是经过优化的分页存储过程完整代码。其核心逻辑是通过动态SQL构建，利用ROW_NUMBER()窗口函数为结果集生成连续行号，从而实现精准的数据切片。
复制代码
存储过程代码如下:
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 /*经常会调用第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. 单表数据分页查询
对于单表分页需求，直接传入查询语句即可。以下示例演示如何从contacts表中分页获取特定类型的联系人数据：
复制代码
调用示例代码如下:
Exec proc_SelectForPager @Sql = 'Select * from contacts a where a.ContactType=1', -- varchar(max) @Order = '', -- varchar(4000) @CurrentPage = 3, -- int @PageSize = 20, -- int @TotalCount = 0 -- int
2. 多表联接分页查询
处理涉及多表关联的复杂分页查询同样简便。您只需构建完整的JOIN语句作为参数传入。以下是一个联接staffOUHIST历史表与Staff员工信息表的实战案例：
复制代码
调用示例代码如下:
Exec proc_SelectForPager @Sql = 'Select a.Staff,a.OU,b.FName+b.FName as Name from staffOUHIST a inner join Staff b on b.ID=a.Staff and a.ExpiryDate=''30001231'' ', -- varchar(max) @Order = '', -- varchar(4000) @CurrentPage = 3, -- int @PageSize = 20, -- int @TotalCount = 0 -- int
关键注意事项与优化建议：在传入@Sql参数时，查询语句内部应避免使用公共表表达式（CTE）。这是因为存储过程内部已使用CTE进行分页逻辑封装，外部嵌套可能导致语法冲突或执行错误。理解这一设计约束，有助于您构建正确、高效的传入SQL，确保分页存储过程稳定运行。
