分页存储过程的几种写法
===================================================
存储过程的好处:减少了解析编译的过过,灵活,执行快
创建分页的存储过程:
分页语法,动态拼接SQL,并执行SQL
存储过程和临时表的创建
临时表应及时关闭
数据库:blogDB
--向标识列中插入数据:
set identity_insert dbo.ArticleDetail on
insert into dbo.ArticleDetail(Art_ID,Art_Title,Art_Author) values(28,'asdfasdf','asdf')
set identity_insert dbo.ArticleDetail off
--row_number() 自动排序函数
select row_number() over (order by art_id) num,* from dbo.ArticleDetail
round() 遵循四舍五入把原值转化为指定小数位数,如:round(1.45,0) = 1;round(1.55,0)=2
floor() 向下舍入为指定小数位数 如:floor(1.45,0)= 1;floor(1.55,0) = 1
ceiling() 向上舍入为指定小数位数 如:ceiling(1.45,0) = 2;ceiling(1.55,0)=2
=======================================================================
************************创建日志分页的存储过程****************************
第一种写法:not in的方式查询:
=======================================================================
--------使用临时表创建存储过程:
alter proc GetPageBuilding
@pageIndex int,--第几页
@pageSize int,--每个显示几条
@totalCount int output, --数据总条数
@pageCount int output --总页数
as
select * into #Temp from (select * from Artcle) as T --创建日志信息的临时表
declare @sql nvarchar(1000) --申明一个存储sql语句的变量
set @sql =('select top('+cast(@pageSize as nvarchar(50))+') * from #Temp where Art_ID not in(select top('+cast((@pageIndex-1) *@pageSize as nvarchar(50))+') Art_ID from #Temp order by Art_ID desc)order by Art_ID desc')
exec (@sql) --从临时表中查询分页数据
select @totalCount=count(1),@pageCount=ceiling((count(Art_ID)+0.0)/@PageSize)from #Temp --获得数据总条数,是总页数
drop table #Temp
例:
--查询第2页的数据,每页3条数据
--排除第一页的数据,取第二页的数据
--输出查询到的分布数据,总的数据条数,总的页数
declare @TC int,@PC int
exec GetPageBuilding 2,3,@TC output,@PC output
select @TC,@PC
--另一种分布的存储过程Row_Number:产生自动编号
=========================================
select * from(select Row_Number() over(order by Art_ID) num ,* from Artcle) t
where t.num >=11 and t.num <=15
--select Row_Number() over(排序条件order by Art_ID) 别名num ,* from 表名Artcle
alter proc GetPagesData
(
@PageIndex int,
@PageSize int,
@RowCount int output,
@PageCount int output
)
as
begin
select @RowCount=count(1),@PageCount=ceiling((count(1)+0.0/@PageSize)) from Artcle
declare @sql nvarchar(500)
set @sql='select * from(select Row_Number() over(order by Art_ID desc) num ,* from Artcle) t
where t.num >='+str((@PageIndex-1)*@PageSize+1) +' and t.num <='+str(@PageIndex*@PageSize)
exec (@sql)
end
declare @RowCount int, @PageCount int
exec GetPagesData 3,5,@RowCount output,@PageCount output
select @RowCount as 总条数,@PageCount as 总页数
select * from dbo.Artcle