通用分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

ALTER PROCEDURE [dbo].[sp_page]
@CurrentPage int,   --@CurrentPage为显示那一页,
@PageSize int,    --@PageSize为每一页显示几行,
@Tcount int,    --@Tcount为表的数量
@Field_Info varchar(500), --@Field_info为要显示的字段可以为*,
@Table_info varchar(500), --@Table_info为要查询的表或视图,
@OtherTable varchar(1000),  --@OtherTable为其他的表名 格式为（',表名,表名'）
@Field_id varchar(1000), --@field_id为按这个字段排序,
@intOrder int,    --@intorder 0为升序排1为降序排,
@otherwhere varchar(1000), --@otherwhere为条件,
@RecordCount int output, --@RecordCount为总行数,
@PageCount int output  --@PageCount为总页数

 

AS
begin
 DECLARE @MinPage int, @MaxPage int
 declare @sql varchar(1000)
 declare @sqlt nvarchar(300)
 declare @order varchar(4)
 declare @strWhere varchar(100)
 declare @sqlp varchar(1000)
 set @Field_Info = replace(@Field_Info,'''','')       --除去@field_info中的'
 set @Table_info = replace(@table_info,'''','')       --除去@table_info中的'
 set @Field_id = replace(@Field_id,'''','')            --除去@field_id中的'
 set @otherwhere = @otherwhere    --将@otherwhere中的'换成''让SQL语句正确释别'
 set @strWhere=' Where (1=1) '
 if @Tcount=1
  set @sqlt = 'SELECT @RecordCount = COUNT(*) FROM ' + @Table_Info
 else
  set @sqlt = 'SELECT @RecordCount = COUNT(*) FROM ' + @Table_Info + @OtherTable
 if @otherwhere like ''
  set @sqlt = @sqlt
 else
  set @sqlt = @sqlt+@strWhere+@otherwhere
 --print @sqlt
 exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output     --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名
 IF @PageSize <= 0
  begin
   set @PageSize = 10
  end
 else if @PageSize > @RecordCount
  begin
   set @pageSize = @RecordCount
  end
 if @RecordCount=0
  set @pagecount=0
 else
 begin
  set @pagecount = @RecordCount / @PageSize
  if ((@RecordCount % @pagesize) != 0)     --如果除不尽则加一页
   begin
    set @PageCount = @RecordCount / @PageSize
    set @PageCount = @pagecount + 1
   end
  else
   begin
    set @pagecount = @RecordCount /@PageSize
   end
 end
 IF @CurrentPage <= 0
  begin
   set @CurrentPage = 1
  end
 else if @CurrentPage > @pagecount
  begin  
   set @currentpage = @pagecount  --如果输入页数大于总页数则符最后一页
  end
 SET @MinPage = (@CurrentPage - 1) * @PageSize + 1
 SET @MaxPage = @MinPage + @PageSize - 1
 set @sqlp=' ) as a where rownumber between ' + convert(varchar(10),@minpage) + ' and '+convert(varchar(10),@maxpage)
 BEGIN
  if @intorder = 0
   set @order = 'asc'
  else
   set @order = 'desc'
  if @Field_Info like ''
   set @field_Info = '*'
  if @Tcount=1
   set @sql = 'select * from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info
  else
   set @sql = 'select * from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + @OtherTable
  if @otherwhere like ''
   set @sql = 'select * from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info
  else
   set @sql = 'select * from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info +  @OtherTable+ @strWhere+ @otherwhere
  
  set @sql=@sql+@sqlp
  --print @sql
  exec(@sql)
 END
--测试
--declare @rcon int
--declare @pcon int
--exec sp_page 2,10,0,'wage.*','wage',',job b,jobinfo c','wage.id',0,'and limiteddate is not null and wage.id=b.id and wage.id=c.id ',@rcon output,@pcon output
--print @rcon
--print @pcon
end