SQLSERVER 高效率分页存储过程

IF OBJECT_ID('[usp_Common_Sel_TableData]') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[usp_Common_Sel_TableData]
END
GO
/*

declare @PageCount int,@RecordCount int
exec [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 output
	
SELECT	@PageCount as N'PageCount',@RecordCount as N'RecordCount'
*/
CREATE PROCEDURE [dbo].[usp_Common_Sel_TableData]
	@TableName		NVARCHAR(2000),			-- 表名
	@ReturnFields	NVARCHAR(1000) = '*',	-- 查询列数
	@PageSize		INT = 10,				-- 每页数目
	@PageIndex		INT = 1,				-- 当前页码
	@FilterString			NVARCHAR(1000) = '',	-- 查询条件
	@SortExpression		NVARCHAR(1000),			-- 排序字段
	@PageCount		INT OUTPUT,				-- 页码总数
	@RecordCount	INT OUTPUT	        	-- 记录总数
  AS

--设置属性
SET NOCOUNT ON

-- 变量定义
DECLARE @TotalRecord INT
DECLARE @TotalPage INT
DECLARE @CurrentPageSize INT
DECLARE @TotalRecordForPageIndex INT

BEGIN
	IF @FilterString IS NULL SET @FilterString=N''
	IF(@FilterString<>'') SET @FilterString=N'WHERE '+@FilterString
	
	-- 记录总数
	DECLARE @countSql NVARCHAR(4000)  
	
	IF @RecordCount IS NULL
	BEGIN
		SET @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@FilterString
		EXECUTE sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord OUT
	END
	ELSE
	BEGIN
		SET @TotalRecord=@RecordCount
	END		
	
	SET @RecordCount=@TotalRecord
	SET @TotalPage=(@TotalRecord-1)/@PageSize+1	
	SET @CurrentPageSize=(@PageIndex-1)*@PageSize

	-- 返回总页数和总记录数
	SET @PageCount=@TotalPage
	SET @RecordCount=@TotalRecord
		
	-- 返回记录
	SET @TotalRecordForPageIndex=@PageIndex*@PageSize
	
	EXEC	('SELECT *
			FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER (ORDER BY '+@SortExpression+') AS PageView_RowNo
			FROM '+@TableName+ ' ' + @FilterString +' ) AS TempPageViewTable
			WHERE TempPageViewTable.PageView_RowNo > 
			'+@CurrentPageSize)
	
END
GO