真正高效的SQLServer中数据分页的通用存储过程

 

/**/ /*=======================================================================  
功能: 对传进来的查询SQL进行分页后返回指定页数据, 不管查询多少页,其查询性能只受 @SqlStr 自身性能的影响,有时还要快
参数: @SqlStr 需要执行的查询语句
      @PrimaryKey 参数@SqlStr返回列中具有唯一确定性的字段名,不能有表别名前缀
      @OrdrStr 参数@SqlStr返回列中存的字段,用于排序,不能有表别名前缀
      @CurrPageNo 需要返回的页数
      @PageSize 每页行数
      @TotalNum 当传进来为 -1 时,返回符合条件的总行数
author: 杨连山
date: 2008-01-25 
测试代码:
SELECT GETDATE()
DECLARE 
  @SqlStr     VARCHAR(8000),
  @CurrPageNo INT,
  @PageSize   INT,
  @TotalNum   INT
SET @SqlStr = 
'SELECT *
FROM PROD_PRODUCT WITH(NOLOCK)
WHERE CONTAINS(ProductName, ''手机'')'
SET @CurrPageNo = 700
SET @PageSize = 20
SET @TotalNum = -1
EXEC COMMON_PROCEDURE_QueryWithPage @SqlStr, 'ProductPKId', 'UpdateDateTime', @CurrPageNo, 20, @TotalNum output
SELECT @TotalNum, GETDATE()
=========================================================================*/   
IF   EXISTS ( SELECT   *   FROM  sysobjects  WHERE  name  =   ' COMMON_PROCEDURE_QueryWithPage '   AND  type  =   ' P ' )
   DROP   PROC  COMMON_PROCEDURE_QueryWithPage
GO
CREATE   PROC  COMMON_PROCEDURE_QueryWithPage
(
     @SqlStr          VARCHAR ( 5000 ),
     @PrimaryKey      VARCHAR ( 100 ),
     @OrderStr        VARCHAR ( 500 ),
     @CurrPageNo      INT ,
     @PageSize        INT ,
     @TotalNum        INT  OUTPUT
)
AS
DECLARE
   @ExeSql   VARCHAR ( 8000 )
SET  NOCOUNT  ON
IF   @TotalNum   =   - 1
   CREATE   TABLE  #Table_Count(TotalNum  INT )
SET   @ExeSql   =  
' SELECT IDENTITY(INT) AS RowIndex, A. '   +   @PrimaryKey   +   CHAR ( 10 )  +
' INTO #Table_Data
FROM ( '   +   @SqlStr   +   ' ) AS A '
IF   @OrderStr   <>   ''
BEGIN
   SET   @ExeSql   =   @ExeSql   +   CHAR ( 10 )  +
     ' ORDER BY  '   +   @OrderStr
END
IF   @TotalNum   =   - 1
BEGIN
   SET   @ExeSql   =   @ExeSql   +   CHAR ( 10 )  +   CHAR ( 10 )  +
     ' INSERT INTO #Table_Count VALUES(ISNULL(SCOPE_IDENTITY(), 0)) '
END
SET   @ExeSql   =   @ExeSql   +   CHAR ( 10 )  +   CHAR ( 10 )  +
' SELECT * 
FROM ( '   +   @SqlStr   +   ' ) AS A
WHERE A. '   +   @PrimaryKey   +   '  IN(SELECT  '   +   @PrimaryKey   +   '  FROM #Table_Data WHERE RowIndex BETWEEN  '   +  
   CONVERT ( VARCHAR , ( @CurrPageNo - 1 ) * @PageSize + 1 )  +   '  AND  '   +   CONVERT ( VARCHAR ,  @CurrPageNo * @PageSize )  +   ' ) '
IF   @OrderStr   <>   ''
BEGIN
   SET   @ExeSql   =   @ExeSql   +   CHAR ( 10 )  +
     ' ORDER BY  '   +   @OrderStr
END

EXEC ( @ExeSql )
IF   @TotalNum   =   - 1
BEGIN
   SELECT   @TotalNum   =  Totalnum  FROM  #Table_Count
   DROP   TABLE  #Table_Count
   IF   @TotalNum   IS   NULL
     SET   @TotalNum   =   - 1
END
RETURN
GO