代码改变世界

分页存储过程(一)使用SQL Server2005的新函数构造分页存储过程

2009-10-20 23:17  Virus-BeautyCode  阅读(8107)  评论(14编辑  收藏  举报

 

使用SQL Server2005的新函数构造分页存储过程

         分页存储过程一直是很多人经常用到的东西,怎么样才能设计一个既实用,又强大,还可以解决大数据量的分页存储过程呢?其实在很多时候设计的度还是要把握的,不至于让自己陷入【非要把它设计成万能的分页存储过程】的怪圈中才是最重要的,因为我们还要留出时间还解决其他的很多问题,个人认为适度就可以了,留出一定的空间。也因为万能是不存在的,万物在一定的范畴之内都是合理的,出了范畴可能就没有合理的了。

         分页存储过程大致有下列几种

1、 利用Not in select top

 

 

2、 利用id大于多少和select top

 

3、 利用sql中的游标

 

 

4、临时表

         可以参看网上的以下链接

         http://www.zxbc.cn/html/20090625/71918.html

http://read.newbooks.com.cn/info/174545.html

         2005中我们的选择就多了,可以利用新语法CTE(公用表表达式),关于CTE的介绍大家可以参看博客园中一位仁兄的系列教程

         http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html

 

         或者干脆上微软的官网

http://msdn.microsoft.com/zh-cn/library/ms190766(SQL.90).aspx

 

查看具体内容。

         除此之外还可以利用在2005中新增的一些函数,分别是:row_number(),rank,dense_rank,ntile,这些新函数是您可以有效的分析数据以及向查询饿结果行提供排序值。您可能发现这些新函数有用的典型方案包括:将连续整数分配给结果行,以便进行表示、分页、计分和绘制直方图。

         详细介绍参见下列链接

        

         http://blog.csdn.net/htl258/archive/2009/03/20/4006717.aspx

 

 

         我这里主要使用的就是row_number()结合新语法CTE,先贴上我的存储过程。设计,开发,测试存储过程和相关的C#代码就花费我两天的时间,不过后面的相似界面就很快了,一上午就可以搞两个分页显示的页面,就算是复杂的查询,一上午也可以搞定。

  下面的存储过程没有将总页数和总条目数返回,如果你有兴趣,可以自己加上,可以参看http://www.zxbc.cn/html/20090625/71918.html中的下列部分

  Declare @sql nvarchar(4000);
Declare @totalRecord int;
--计算总记录数
if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数

--计算总页数

select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize)

   

    存储过程SQL如下,支持不定列,不定条件,多表联合,排序任意

 

 

代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go







 

 
--Declare @sql nvarchar(4000); 
--
Declare @totalRecord int; 
--
--计算总记录数 
--
if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL) 
--
set @sql = 'select @totalRecord = count(*) from ' + @TableName 
--
else 
--
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere 
--
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 
--
--
--计算总页数 
--
--
select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize) 

-- =============================================

-- Author:    shiwenbin

-- MSN:    jorden008@hotmail.com

-- Email:    jorden008@163.com

-- Create date: 2009-10-20

-- Description:   分页存储过程,根据传递的参数返回分页的结果

-- Parameters:

-- =============================================
ALTER PROCEDURE [dbo].[Proc_GetDataPaged] 

    
-- Add the parameters for the stored procedure here

    

    
@StrSelect varchar(max)=null,    --欲显示的列(多列用逗号分开),例如:id,name

    
@StrFrom varchar(max)= null,    --表名称,或者是表连接字符串,多表连接例如:student as s inner join dwinfo as dw on s.dwbh=dw.bh

    
@StrWhere varchar(max)=null,    --查询条件,''代表没有条件,单条件或者多条件,多条件例如:name='啊' and id=10

    
@StrOrder varchar(max=null,   --排序列(多个排序列用逗号分开),例如:id desc,name as 

    
--@PageCount int output,     --总页数

    
@ItemCount bigint output,     --总记录数

    
@PageSize int =50,     --每页显示条数
    @BeginIndex int=1,--记录开始数
    @DoCount bit =0 --是否统计总数,为0不统计,为1统计
 --   @PageIndex int =1     --当前页

    
--@ClassCode char(10) =null,    --单位编号(班级编号)   

AS

BEGIN

    
SET NOCOUNT ON;

     
    
Declare @sql nvarchar(4000); 
    
Declare @totalRecord int
    
--计算总记录数 
    if (@StrWhere ='''' or @StrWhere='' or @StrWhere is NULL
    
set @sql = 'select @totalRecord = count(*) from ' + @StrFrom 
    
else 
    
set @sql = 'select @totalRecord = count(*) from ' + @StrFrom + ' where ' + @StrWhere 
    
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@ItemCount OUTPUT--计算总记录数 

    
declare @SqlQuery varchar(max)

  
-- if(@PageIndex=1)
  if(@BeginIndex=1 or @BeginIndex=0  or @BeginIndex <0)
       
begin

        
if(@StrWhere is null)--if(@StrWhere='')

        
set @SqlQuery='select top '+convert(varchar,@PageSize)

          
+ ' row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+

       
' from '+@StrFrom;

        
else

           
--set @sql='select top @PageSize * from @TableName order by id desc';

        
--select top @PageSize * from @TableName order by id desc;

        
set @SqlQuery='select top '+convert(varchar,@PageSize)

          
+ ' row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+' where '+@StrWhere;

        
--exec (@SqlQuery)

--      @SqlQuery

       
end

    
else         

       
begin

        
if(@StrWhere is null)--if(@StrWhere='')

        
begin

           
set @SqlQuery='with cte as (

select row_number() over(order by 
'+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+'

)
select * from cte where RowNumber between 
'+ 

--convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
--
--
      convert(varchar,@PageIndex*@PageSize)           
convert(varchar,@BeginIndex)+' and '+

      
convert(varchar,@BeginIndex+@PageSize)   
           
--print @SqlQuery

        
end

        
else

        
begin

          

           
set @SqlQuery='with cte as (

select row_number() over(order by 
'+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+' where '+@StrWhere+'

)

select * from cte where RowNumber between 
'+ 
--convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
--
--
        convert(varchar,@PageIndex*@PageSize)
convert(varchar,@BeginIndex)+' and '+

      
convert(varchar,@BeginIndex+@PageSize)  
           
--print @SqlQuery

          
end

       
end

    
--set @SqlQuery=@SqlQuery+';select @ItemCount =count(*) from '+@TableName

    
--set @PageCount=@ItemCount/@PageSize

    
--print '共'+@PageConut+'页'+@ItemCount+'条'

    
--print @ItemCount

    
print @SqlQuery

    
exec (@SqlQuery)

END

 






 

 

   c#相关代码的数据库访问使用的是微软的企业库 V4.1

  

  Enterprise Library 4.1 下载地址:

 

   示例代码,前台页面,前台为用户控件

  

Code

 

   示例代码,后台代码

  

Code

 

最后再贴一个圆友的通用存储过程,原文地址:通用存储过程分页(使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况)性能分析

 

代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- =============================================
--
 Author:        <jiangrod>
--
 Create date: <2010-03-03>
--
 Description:    <SQL2005及后续版本通用分页存储过程>调用方法: sp_Pager2005 'xtest','*','ORDER BY ID ASC','xname like ''%222name%''',2,20,0,0
--
                    适合从单个表查询数据
--
 =============================================
ALTER PROCEDURE [dbo].[Proc_GetDataPaged2]
@tblName   varchar(255),            -- 表名如:'xtest'
@strGetFields varchar(1000= '*',  -- 需要返回的列如:'xname,xdemo'
@strOrder varchar(255)='',          -- 排序的字段名如:'order by id desc'
@strWhere  varchar(1500= '',      -- 查询条件(注意:不要加where)如:'xname like ''%222name%''' 
@beginIndex int=1,                  --开始记录位置
--
@pageIndex  int = 1,                -- 页码如:2
@pageSize   int = 50,               -- 每页记录数如:20
@recordCount int output,            -- 记录总数
@doCount bit=0                        -- 非0则统计,为0则不统计(统计会影响效率)
AS

declare @strSQL varchar(5000)
declare @strCount nvarchar(1000)
--总记录条数
if(@doCount!=0)
begin
    
if(@strWhere !='')
    
begin
        
set @strCount='set @num=(select count(1) from '+ @tblName + ' where '+@strWhere+' )'
    
end
    
else
    
begin
        
set @strCount='set @num=(select count(1) from '+ @tblName + ' )'
    
end
    
EXECUTE sp_executesql @strCount ,N'@num INT output',@RecordCount output
end

if @strWhere !=''
begin
    
set @strWhere=' where '+@strWhere
end
set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
set @strSQL=@strSQL+@strGetFields+' FROM ['+@tblName+''+@strWhere
set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str(@beginIndex)
set @strSQL=@strSQL+' AND '+str(@beginIndex+@PageSize)
--set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--
set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
exec (@strSQL)




 

 

  再来一个

  

 

 

代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






-- =============================================
--
 Author:        <Author,,Name>
--
 Create date: <Create Date,,>
--
 Description:    分页获取商品信息
--
调用方法: Proc_GetProductPaged '2','*','','xname like ''%222name%''','ORDER BY ID ASC',20,2,0,0
--
 =============================================
ALTER PROCEDURE [dbo].[Proc_GetProductPaged]
    
-- Add the parameters for the stored procedure here
    @ProductType smallint=1,--商品类型,1全部2种子3农药4肥料
    @StrSelect varchar(max)='',--显示字段
    @StrFrom varchar(max)='',--查询来源
    @StrWhere varchar(max)='',--查询条件
    @StrOrder varchar(max)='',--排序规范
    @PageSize int=50,--记录数
    @BeginIndex int=1,                  --开始记录位置
--
    @PageIndex int=1,--页码
    @Count int output,--记录总数
    @DoCount bit=0-- 1则统计,为0则不统计(统计会影响效率)
AS
BEGIN
    
-- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    declare @seedtype int
    
set @seedtype=2
    
declare @pestype int
    
set @pestype=3
    
declare @ferttype int
    
set @ferttype=4
    
    
create table #product
    (    
      productid 
uniqueidentifier,
      productname 
varchar(50),
      className 
varchar(50),
      productType 
int,
      createdate 
datetime,
      modifydate 
datetime
    
--  companyid uniqueidentifier
    )
    
declare @strSQL varchar(max)
    
declare @strCount nvarchar(max)
    
--计算总记录条数
    if(@DoCount!=0)
    
begin
        
if(@StrWhere !='')
        
begin
            
if(@ProductType=1)
                
begin
                    
set @strCount='set @num=(select count(1) from Seed where '+@StrWhere+' )'+
                    
'+(select count(1) from pesticide where '+@StrWhere+' )'+
                    
'+(select count(1) from fertilizer where '+@StrWhere+' )'
                
end
            
else if(@ProductType=2)
                
begin
                    
set @strCount='set @num=(select count(1) from Seed where '+@StrWhere+' )'
                
end
            
else if(@ProductType=3)
                
begin
                    
set @strCount='set @num=(select count(1) from pesticide where '+@StrWhere+' )'
                
end
            
else if(@ProductType=4)
                
begin
                    
set @strCount='set @num=(select count(1) from fertilizer where '+@StrWhere+' )'
                
end            
        
end
        
else
        
begin
            
if(@ProductType=1)
                
begin
                    
set @strCount='set @num=(select count(1) from Seed )'+
                    
'+(select count(1) from pesticide  )'+
                    
'+(select count(1) from fertilizer )'
                    
                
end
            
else if(@ProductType=2)
                
begin
                    
set @strCount='set @num=(select count(1) from Seed )'
                
end
            
else if(@ProductType=3)
                
begin
                    
set @strCount='set @num=(select count(1) from pesticide )'
                
end
            
else if(@ProductType=4)
                
begin
                    
set @strCount='set @num=(select count(1) from fertilizer )'
                
end    
        
end
        
EXECUTE sp_executesql @strCount ,N'@num INT output',@Count output
    
end
    
--分页获取数据
    if (@StrWhere !='' )
    
begin
        
set @StrWhere=' where '+@StrWhere
    
end
    
if(@ProductType=1)
        
begin
            
set @strSQL='insert into #product 
            select s.seedid,s.seedname,cc.cropclassname,
'+cast(@seedtype as varchar(1))+',s.createdate,s.modifydate
            from seed as s inner join cropclass as cc on s.cropclasscode=cc.cropclasscode
'
            
+@StrWhere+
            
'union
            select p.pesticideid,p.pesname,pc.pesclassname,
'+cast(@pestype as varchar(1))+',p.createdate,p.modifydate
            from pesticide as p inner join pesclass as pc on p.pesclasscode=pc.pesclasscode
'
            
+@StrWhere+
            
'union
            select f.fertilizerid,f.fertname,fc.fertclassname,
'+cast(@ferttype as varchar(1))+',f.createdate,f.modifydate
            from fertilizer as f inner join fertilizerclass as fc on f.fertclasscode=fc.fertclasscode
'
            
+@StrWhere

            
set @strSQL= @strSQL+' SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@StrOrder+') AS ROWID,'
            
set @strSQL=@strSQL+'* FROM [#product] '--+@StrWhere
            set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str(@BeginIndex)
            
set @strSQL=@strSQL+' AND '+str(@BeginIndex+@PageSize-1)
--            set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--
            set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
        end
    
else
        
begin
            
set @strSQL='insert into #product select '+@StrSelect+
            
' from '+@StrFrom+@StrWhere
            
exec (@strSQL)
            
set @strSQL=''
            
set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
            
set @strSQL=@strSQL+'* FROM [#product]'+@strWhere
            
set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str(@BeginIndex)
            
set @strSQL=@strSQL+' AND '+str(@BeginIndex+@PageSize-1)
--            set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--
            set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
        end
--    else if(@ProductType=2)
--
        begin
--
            set @strSQL='insert into #product select '+@StrSelect+','+@seedtype+
--
            'from '+@StrFrom+@StrWhere
--
            exec @strSQL
--
            set @strSQL=''
--
            set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
--
            set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@strWhere
--
            set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--
            set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
--
        end
--
    else if(@ProductType=3)
--
        begin
--
            set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
--
            set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@StrWhere
--
            set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--
            set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
--
        end
--
    else if(@ProductType=4)
--
        begin
--
            set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
--
            set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@StrWhere
--
            set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--
            set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
--
        end            
  
    
exec (@strSQL)
    
    
drop table #product
END