if object_ID('[proc_SelectForPager]') is not null Drop Procedure [proc_SelectForPager]GoCreate 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 /*經(jīng)常會調(diào)用第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
