图片 2

自己动手写一个通用的分页存储过程(适用于多表查询)

复制代码 代码如下:create proc
commonPagination @columns varchar(500卡塔尔, –要出示的列名,用逗号隔开@tableName varchar(100卡塔尔(قطر‎, –要查询的表名 @orderColumnName varchar(100State of Qatar,
–排序的列名 @order varchar(50卡塔尔, –排序的形式,升序为asc,降序为 desc
@where varchar(100State of Qatar, –where 条件,假若不带查询条件,请用 1=1 @pageIndex
int, –当前页索引 @pageSize int, –页大小(每页显示的笔录条数卡塔尔(قطر‎ @pageCount
int output –总页数,输出参数 as begin declare @sqlRecordCount
nvarchar(1000卡塔尔国 –获得总记录条数的口舌 declare @sqlSelect nvarchar(1000卡塔尔(قطر‎–查询语句 set @sqlRecordCount=N’select @recordCount=count(*卡塔尔国 from ‘
+@tableName + ‘ where ‘+ @where declare @recordCount int
–保存总记录条数的变量 exec sp_executesql
@sqlRecordCount,N’@recordCount int output’,@recordCount output –动态
sql 传参 if( @recordCount % @pageSize = 0卡塔尔–若是总记录条数能够被页大小整除 set @pageCount = @recordCount /
@pageSize –总页数就也正是总记录条数除以页大小 else
–假如总记录条数无法被页大小整除 set @pageCount = @recordCount /
@pageSize + 1 –总页数就也正是总记录条数除以页大小加1 set @sqlSelect =
N’select ‘+@columns+’ from ( select row_number() over (order by ‘
+@orderColumnName+’ ‘+@order +’) as tempid,* from ‘ +@tableName+’ where
‘+ @where +’) as tempTableName where tempid between ‘ +str((@pageIndex –
1)*@pageSize + 1 ) +’ and ‘+str( @pageIndex * @pageSizeState of Qatar exec
(@sqlSelectState of Qatar –实行动态Sql end go –以下是调用示例 use pubs go declare
@pageCount int exec commonPagination
‘job_id,job_desc’,’jobs’,’job_id’, ‘asc’,’1=1′,2,2,@pageCount output
select ‘总页数为:’ + str(@pageCount卡塔尔

图片 1

 

图片 2

Create
Procedure usp_Paging1
@tableName nvarchar(50卡塔尔(قطر‎,–要分页的表,借使是多表查询,请使用
Student,Score,Class的款式。
@primaryKey nvarchar(20卡塔尔,–表的主键,该主键字段用于Row_Number函数的
over(order by)中
@orderCondition nvarchar(100卡塔尔,–排序条件,如 id desc,name asc
@pageIndex int=1,–当前要率先的第几页,暗中认可彰显第一页
@pageSize int=10,–每页大小,私下认可每页呈现10条
@pageCondition
nvarchar(100卡塔尔(قطر‎,–用于where部分的标准,先帅选出多少在分页,假如是多表查询,这里就亟须带有相似于Student.id=score.id的样式
@rowCount int output,–记录总共有多少条记下
@pageCount int output
as
begin
   declare @sql nvarchar(max卡塔尔国,@sqlPaging nvarchar(max卡塔尔,@total
int–@total用来记录有个别条件下的笔录总量
   set @sql=N’select @count=count(*) from ‘+@tableName
   set @sqlPaging=N’select * from (select Row_Number() over(order by
id) RowIndex,* from ‘+@tableName+’) As tmp ‘
   –假设分页是有原则的
   if(@pageCondition is not NULL and
len(RTRIM(LTRIM(@pageCondition)))>0)
   begin
      set @sql=@sql+N’ where
‘+@pageCondition–求出钦点条件下的某些许条记下
      set @sqlPaging=N’select * from (select Row_Number() over(order
by ‘+@primaryKey+’) RowIndex,* from ‘+@tableName+’ where 
‘+@pageCondition+’) As tmp ‘
   end
   –取得总共有多少条记下
   exec sp_executesql @stmt=@sql,@params=N’@count int
output’,@count=@total output
   set @rowCount=@total
   set @pageCount=ceiling((@total*1.0/@pageSize卡塔尔卡塔尔国–求出一同有稍许页

   set @sqlPaging=@sqlPaging+N’where tmp.RowIndex between
‘+Convert(varchar(50),((@pageIndex-1)*@pageSize+1))+’ and
‘+cast((@pageIndex*@pageSize) as varchar(50))
   if(@orderCondition is not null and
len(RTRIM(LTRIM(@orderCondition)))>0)
   begin
      set @sqlPaging=@sqlPaging+’ order by ‘+ @orderCondition
   end
   exec sp_executesql @stmt=@sqlPaging
end
go
declare @sum int,@rowCount int
exec usp_Paging1
@tableName=’Student’,@primaryKey=’id’,@orderCondition=’id
desc’,@pageIndex=1,@pageSize=20,@pageCondition=’id>10′,
     @pageCount=@sum output,@rowCount=@rowCount output
select @sum as 总页数,@rowCount 总记录条数