SQLServer2005通用分页存储过程及多表联接应用

ifobject_ID("[proc_SelectForPager]")isnotnull
DropProcedure[proc_SelectForPager]
Go
CreateProcproc_SelectForPager
(
@Sqlvarchar(max),
@Ordervarchar(4000),
@CurrentPageint,
@PageSizeint,
@TotalCountintoutput
)
As
/*Andy2012-2-28*/
Declare@Exec_sqlnvarchar(max)
Set@Exec_sql="Set@TotalCount=(SelectCount(1)From("+@Sql+")Asa)"
Execsp_executesql@Exec_sql,N"@TotalCountintoutput",@TotalCountoutput
Set@Order=isnull("Orderby"+nullif(@Order,""),"OrderBygetdate()")
if@CurrentPage=1/*经常会调用第1页，这里做特殊处理，少一层子查询*/
Set@Exec_sql="
;WithCTE_ExecAs
(
"+@Sql+"
)
SelectTop(@pagesize)*,row_number()Over("+@Order+")AsrFromCTE_ExecOrderByr
"
Else
Set@Exec_sql="
;WithCTE_ExecAs
(
Select*,row_number()Over("+@Order+")AsrFrom("+@Sql+")Asa
)
Select*FromCTE_ExecWhererBetween(@CurrentPage-1)*@pagesize+1And@CurrentPage*@pagesizeOrderByr
"
Execsp_executesql@Exec_sql,N"@CurrentPageint,@PageSizeint",@CurrentPage,@PageSize
Go