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 */
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

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

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