简单的分页存储过程！

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 ....，总觉得效率不高，希望以后能找到点好办法。先记录在此。