SQLSERVER 高效率分页存储过程

IF OBJECT_ID('[usp_Common_Sel_TableData]') IS NOT NULLBEGINDROP PROCEDURE [dbo].[usp_Common_Sel_TableData]ENDGO/*declare @PageCount int,@RecordCount intexec [usp_Common_Sel_TableData] @TableName=N'vw_Sale_SaleContract_All',@ReturnFields=N'*',@PageSize=10,@PageIndex=1,@FilterString=N'CompanyID=309',@SortExpression=N'CreateTime DESC',@PageCount=@PageCount output,@RecordCount=@RecordCount outputSELECT@PageCount as N'PageCount',@RecordCount as N'RecordCount'*/CREATE PROCEDURE [dbo].[usp_Common_Sel_TableData]@TableNameNVARCHAR(2000),-- 表名@ReturnFieldsNVARCHAR(1000) = '*',-- 查询列数@PageSizeINT = 10,-- 每页数目@PageIndexINT = 1,-- 当前页码@FilterStringNVARCHAR(1000) = '',-- 查询条件@SortExpressionNVARCHAR(1000),-- 排序字段@PageCountINT OUTPUT,-- 页码总数@RecordCountINT OUTPUT        -- 记录总数  AS--设置属性SET NOCOUNT ON-- 变量定义DECLARE @TotalRecord INTDECLARE @TotalPage INTDECLARE @CurrentPageSize INTDECLARE @TotalRecordForPageIndex INTBEGINIF @FilterString IS NULL SET @FilterString=N''IF(@FilterString<>'') SET @FilterString=N'WHERE '+@FilterString-- 记录总数DECLARE @countSql NVARCHAR(4000)  IF @RecordCount IS NULLBEGINSET @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@FilterStringEXECUTE sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord OUTENDELSEBEGINSET @TotalRecord=@RecordCountENDSET @RecordCount=@TotalRecordSET @TotalPage=(@TotalRecord-1)/@PageSize+1SET @CurrentPageSize=(@PageIndex-1)*@PageSize-- 返回总页数和总记录数SET @PageCount=@TotalPageSET @RecordCount=@TotalRecord-- 返回记录SET @TotalRecordForPageIndex=@PageIndex*@PageSizeEXEC('SELECT *FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER (ORDER BY '+@SortExpression+') AS PageView_RowNoFROM '+@TableName+ ' ' + @FilterString +' ) AS TempPageViewTableWHERE TempPageViewTable.PageView_RowNo > '+@CurrentPageSize)ENDGO