SQL SERVER 存储过程分页的3种通用方法

       以前用程序写了很多效率不错分页方法，但很麻烦也都不通用。最近抽空编写了用SQL SEVER后台存储过程实现分页通用的方法，此过程提供3种分页技术方法，可以根据具体情况采用不同的方法。

--exec get_page 'T_Table,'*','xh',100,6,0,0,' ',' lb like ''1%'''

CREATE  PROCEDURE get_page
@tblName   varchar(255),       -- 表名
@strGetFields varchar(1000) = '*',  -- 需要返回的列 
@fldName varchar(255)='',      -- 排序的字段名
@PageSize   int = 10,          -- 页尺寸
@PageIndex  int = 1,           -- 页码
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序
@OtherOrder varchar(200),  --其它排序字段
@strWhere  varchar(1500) = '',  -- 查询条件 (注意: 不要加 where)
@Type varchar(1)='1'        --分页使用方法
AS
SET NOCOUNT ON 
declare @strSQL   varchar(5000)       -- 主语句
declare @strTmp   varchar(110)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型

if @doCount != 0
  begin
    if @strWhere !=''
    set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere
    else
    set @strSQL = 'select count(*) as Total from ' + @tblName + ''
end  
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin

if @OrderType != 0
begin
    set @strTmp = '<(select min'
    if @OtherOrder!='' 
      set @strOrder = ' order by ' + @fldName +' desc,'+@OtherOrder+' '
    else
      set @strOrder = ' order by ' + @fldName +' desc' 
    --如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
    set @strTmp = '>(select max'
    if @OtherOrder!='' 
      set @strOrder = ' order by ' + @fldName +' asc,'+@OtherOrder+' '
    else
      set @strOrder = ' order by ' + @fldName +' asc'
end

if @PageIndex = 1
begin
    if @strWhere != ''   
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' + @tblName + ' where ' + @strWhere + ' ' + 

@strOrder
     else
     set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName + ' '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
    IF @Type='1'
    BEGIN--方法1，采用嵌套SELECT语句与TOP方法，在数据量不大的情况效果最好。
        set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '
            + @tblName + ' where ' + @fldName + '' + @strTmp + '('+ @fldName + ') from (select top ' + str

((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + @strOrder + ') as tblTmp)'+ @strOrder
        
        if @strWhere != ''
            set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '
                + @tblName + ' where ' + @fldName + @strTmp + '('
                + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
                + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
                + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
        EXEC (@strSQL)
    END
    ELSE
    IF @Type='2'
    BEGIN --方法2，采用一个轻量级的中间表，在大数据量的情况下效果会比较明显
        CREATE TABLE #t(Fkey varchar(1000))
        DECLARE @i int
    
        SET @i=@PageIndex*@PageSize   
        IF @strWhere ='' 
            SET @strWhere = '1=1'
        SET @strSQL ='INSERT INTO #t SELECT '+@fldName+' FROM '+@tblName+' WHERE '+@strWhere+' '+ @strOrder
        SET ROWCOUNT @i
        print @strSQL
        EXEC(@strSQL)
    
        SET @i=@i-@pagesize   
        SET ROWCOUNT @i
        DELETE FROM #t
        
        --返回查询结果   
        SET @strSQL ='SELECT '+@strGetFields+' FROM '+@tblName+' AS a  WHERE EXISTS(SELECT FKey FROM #t WHERE FKey=a.'+@fldName+') '+ @strOrder
        print @strSQL        
        EXEC(@strSQL)
        SET ROWCOUNT 0  
        DROP TABLE #t
        RETURN
    END
    ELSE
    IF @Type='3'
    BEGIN --方法3，采用取当前开始编号与结束编号进行取数据集 
        DECLARE @TheSQL NVARCHAR(4000)
        DECLARE @RowCount INT
        DECLARE @BeginID VARCHAR(1000)
        DECLARE @EndID VARCHAR(1000)
        
        /*开始编号*/
        SET @RowCount = (@PageIndex -1)*@PageSize +1
        SET ROWCOUNT @RowCount
        SET @TheSQL = 'SELECT @BeginID = '+@fldName+' FROM '+@tblName+ ' WHERE '+@strWhere+' '+ @strOrder
        EXEC sp_executesql @TheSQL,N'@BeginID VARCHAR(1000) output',@BeginID output     
    
        /*结束编号*/
        SET @RowCount = @PageIndex * @PageSize
        SET ROWCOUNT @RowCount
        SET @TheSQL = 'SELECT @EndID = '+@fldName+' FROM '+@tblName + ' WHERE '+@strWhere+' '+ @strOrder
        EXEC sp_executesql @TheSQL,N'@EndID varchar(1000) output',@EndID output     
        /*返回当前页结果集*/
        SET ROWCOUNT @PAGESIZE 
        EXEC('select '+@strGetFields+' from   '+@tblName+' WHERE( '+@fldName+' between '''+@EndID +''' and '''+@BeginID+''') ' + @strOrder)

        SET ROWCOUNT 0    
        SET NOCOUNT OFF
        RETURN
    END

end 
end   


GO

                                                                                      作者QQ:43460043 MSN:[e-mail]