真正高效的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