简单的分页存储过程！

比眉伴天荒 2021-11-05 01:20 406阅读 0赞

1.模拟数据

use xacus;
    --creat some data
    declare @serial int
    set @serial=1
    while @serial<100
    begin
      insert into KHZL(SEARIL) values(@serial)
      set @serial=@serial+1;
    end

2.存储过程

CREATE proc proc_splitPage
    (
    --@tableName nvarchar(100),         --表名
    @whereCondition nvarchar(100),      --where条件
    @pageSize int,                      --每页条数
    @pageIndex int,                     --第几页              
    @pageRecordNum int output,          --返回总记录数
    @pageNum int output                 --返回总页数
    )
    as
    set nocount on
    declare @sql nvarchar(1000)       --sql执行
    
    
    
    --start
    begin
    
    --set params
    
    --set @sql=N'select @pageRecordNum=count(*) from KHZL'
    --exec proc_splitPage @sql,N'@pageRecordNum int output',@pageRecordNum output
    declare @count nvarchar(100)
    set @count=N'select @pageRecordNum=count(*) from KHZL where '+@whereCondition
    exec sp_executesql @count,N'@pageRecordNum int output',@pageRecordNum output
    print @count
    
    
    
    if @pageSize>=@pageRecordNum
      begin
        set @pageNum=1 --只有1页
        set @pageSize=@pageRecordNum
      end
    else
      begin
      if @pageRecordNum%@pageSize=0
        begin
          set @pageNum=@pageRecordNum/@pageSize
        end
      else 
        begin
          set  @pageNum=@pageRecordNum/@pageSize+1
        end
      end
    
    --split page query begin
    if @pageIndex<=1
      begin 
        set @sql='select top '+ cast(@pageSize as nvarchar)+
       ' KHMC,YHDM,GSMC,NSRSBH,LXDH,SSBM,KHHMC,YHZH,REMARK,POSTADDRESS,OPERATOR,OPERATIONTIME from KHZL '+
       'where '+@whereCondition
        exec(@sql)
        print @sql
      end
    
    else if @pageIndex>@pageNum
      begin
        if @pageNum=1
          begin 
            set @sql='select KHMC,YHDM,GSMC,NSRSBH,LXDH,SSBM,KHHMC,YHZH,REMARK,POSTADDRESS,OPERATOR,OPERATIONTIME from KHZL '+'where '+@whereCondition
            exec(@sql)
            print @sql 
          end
        else
          begin
            set @sql='select KHMC,YHDM,GSMC,NSRSBH,LXDH,SSBM,KHHMC,YHZH,REMARK,POSTADDRESS,OPERATOR,OPERATIONTIME from KHZL where SEARIL not in (select top '+cast(((@pageNum-1)*@pageSize)as nvarchar)+' SEARIL from KHZL '+'where '+@whereCondition+')'
                
            exec(@sql)
            print @sql 
          end
      end
    
    else
      begin
        declare @lastPageSize int--剩余的页数
        set @lastPageSize=@pageRecordNum-((@pageIndex-1)*@pageSize)
        if @lastPageSize<=@pageSize--不能再显示整个@pageSize了
          begin
            set @sql='select top '+cast( @lastPageSize as nvarchar(100))+
            ' KHMC,YHDM,GSMC,NSRSBH,LXDH,SSBM,KHHMC,YHZH,REMARK,POSTADDRESS,OPERATOR,OPERATIONTIME from KHZL where SEARIL not in (select top '+cast(((@pageIndex-1)*@pageSize)as nvarchar)+ ' SEARIL from KHZL '+'where '+@whereCondition+')'
            exec(@sql)
            print @sql
          end
        else
          begin
            set @sql='select top '+cast( @pageSize as nvarchar(100))+
            ' KHMC,YHDM,GSMC,NSRSBH,LXDH,SSBM,KHHMC,YHZH,REMARK,POSTADDRESS,OPERATOR,OPERATIONTIME from KHZL where SEARIL not in (select top '+cast(((@pageIndex-1)*@pageSize)as nvarchar)+ ' SEARIL from KHZL '+'where '+@whereCondition+')'
            exec(@sql)
            print @sql
          end
      end
      
    --end
    end
    
    GO

3.测试

USE XACUS;
    declare @i int,@j int;
    exec proc_splitPage '1=1',10,1,@i output,@j output;
    select @i as 总记录数,@j as 总页数;
    go

4.总结

以上测试环境在MS SQL SERVER 2000.。由于时间紧迫，赶的很，实现的这个存储过程太过鸡肋，既没传表明也没有任何约束，很不通用。不过幸好数据量很小，凑合着用，以后有时间了一定要优化。另外里面内层核心的查询还是用的select...not in ....，总觉得效率不高，希望以后能找到点好办法。先记录在此。