分页存储过程的几种写法 =================================================== 存储过程的好处:减少了解析编译的过过,灵活,执行快 创建分页的存储过程: 分页语法,动态拼接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
转载于:https://www.cnblogs.com/lt-style/p/3413466.html