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

原创 2007年09月21日 15:23:00

       以前用程序写了很多效率不错分页方法,但很麻烦也都不通用。最近抽空编写了用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:wuchencan@hotmail.com.cn

 

sql server 带输入输出参数的分页存储过程(效率最高)

create procedure proc_page_withtopmax ( @pageIndex int,--页索引 @pageSize int,--每页显示数 @pageCount in...
  • kingmax54212008
  • kingmax54212008
  • 2015年07月14日 11:34
  • 5735

SQLServer分页查询通用存储过程

自开始做项目以来,一直在用。这段存储过程的的原创者(SORRY,忘记名字了),写得这段SQL代码很不错,我在这个基础上,按照我的习惯以及思维方式,调整了代码,只做分页查询用。/**//*-------...
  • fcrpg2005
  • fcrpg2005
  • 2007年02月22日 22:09
  • 11592

sqlServer分页存储过程的调用

现象: sqlServer的分页一直相对比较复杂。这里使用存储过程实现分页逻辑解决办法 1:action获取查询的条件,初始化每页显示的大小,page代表当前查看第几页,默认设置为第一页。rows...
  • hcwbr123
  • hcwbr123
  • 2017年04月28日 11:39
  • 579

sql server分页用动态存储过程

页面
  • yibingde
  • yibingde
  • 2014年08月30日 02:22
  • 417

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

 /**//*=======================================================================  功能: 对传进来的查询SQL进行分页后返...
  • bloglife
  • bloglife
  • 2008年02月15日 22:20
  • 2052

Sql Server 分页存储过程

分页存储过程一: --/*-----存储过程 分页处理 孙伟 2005-03-28创建 -------*/ --/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 --...
  • xunzaosiyecao
  • xunzaosiyecao
  • 2015年01月23日 21:12
  • 3690

SQL server 2008 分页存储过程

GO /****** Object: StoredProcedure [dbo].[T_GetPager] Script Date: 03/23/2015 14:35:01 ******/ S...
  • xundh
  • xundh
  • 2015年03月23日 14:36
  • 1189

SqlServer分页存储过程(多表查询,多条件排序),Repeater控件呈现数据以及分页

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如...
  • limingblogs
  • limingblogs
  • 2016年04月25日 21:53
  • 1407

SQLServer千万数量级分页公共存储过程

转自:http://blog.csdn.net/tem168/article/details/6427123 SQLServer千万数量级分页公共存储过程   通用的数据库分页存...
  • chelen_jak
  • chelen_jak
  • 2014年12月11日 21:24
  • 3194

多表联合查询sql存储过程带分页

多表联合查询sql存储过程带分页
  • mofijeck
  • mofijeck
  • 2015年03月04日 14:56
  • 1533
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL SERVER 存储过程分页的3种通用方法
举报原因:
原因补充:

(最多只允许输入30个字)