数据海量分页存储过程（1）

USE [ForePress]
GO
/****** 对象:  StoredProcedure [dbo].[usp_DownloadData]    脚本日期: 02/28/2008 19:06:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--分页读取井数据存储过程

ALTER PROCEDURE [dbo].[usp_DownloadData]

@tblName VARCHAR(255), -- 表名
@PageIndex INT = 0 , -- 页码
@strWhere VARCHAR(1000) = '', --查询条件(不要加WHERE)
@strOrder VARCHAR(1000) = '' , --需要排序的列
@strGetFields VARCHAR(1000) = '*', -- 需要返回的列
@PageSize INT = 200 -- 页尺寸

AS

DECLARE @strSQL VARCHAR(5000) -- 主语句

BEGIN
IF @PageIndex = 0 --若查询第一页,使用以下语句
 BEGIN
   IF @strOrder != ''
   BEGIN
    IF @strWhere != ''
     SET @strSQL = 'SELECT TOP ' + STR(@PageSize) +' ' + @strGetFields
      + ' FROM [' + @tblName + '] ' + ' WHERE ' + @strWhere
      + ' ORDER BY ' + @strOrder
    ELSE
     SET @strSQL = 'SELECT TOP ' + STR(@PageSize) +' ' + @strGetFields
      + 'FROM [' + @tblName + '] ORDER BY ' + @strOrder  
   END
  ELSE
   BEGIN
    IF @strWhere != ''
     SET @strSQL = 'SELECT ' +' ' + @strGetFields
      + ' FROM [' + @tblName + '] ' + 'WHERE ' + @strWhere
    ELSE
     SET @strSQL = 'SELECT ' + @strGetFields + ' FROM [' + @tblName + '] '
      
   END
 END
ELSE --处理非第一页的SQL语句
 BEGIN
   IF @strOrder != ''
   BEGIN
    IF @strWhere != ''
     SET @strSQL = 'SELECT TOP ' + STR(@PageSize) +' ' + @strGetFields
      + ' FROM [' + @tblName + '] '
      + ' WHERE ' + @strOrder + ' > ' + '( SELECT MAX('+ @strOrder
      + ') FROM  ( SELECT TOP ' + STR(@PageIndex*@PageSize)+ ' '
      + @strOrder + ' FROM [' + @tblName + '] WHERE ' + @strWhere
      + ' ORDER BY ' + @strOrder + ') AS T ) ORDER BY ' + @strOrder
      
    ELSE
     SET @strSQL = ' SELECT TOP ' + STR(@PageSize) +' ' + @strGetFields
      + ' FROM [' + @tblName + '] WHERE ' + @strOrder + ' > '
      + '(SELECT MAX( ' + @strOrder + ' ) FROM ( SELECT TOP ' + STR(@PageIndex*@PageSize)
      +  @strOrder + ' FROM [ ' + @tblName + '] ' + 'ORDER BY ' + @strOrder
      + ') AS T )' + 'ORDER BY ' + @strOrder
   END
  ELSE
   BEGIN
    IF @strWhere != ''
     SET @strSQL = 'SELECT ' +' ' + @strGetFields
      + ' FROM [' + @tblName + '] ' + ' WHERE ' + @strWhere
    ELSE
     SET @strSQL = 'SELECT ' +' ' + @strGetFields
      + ' FROM [' + @tblName + '] '
   END

 END
END

EXEC (@strSQL)