-- (输入班学生姓名 计算总记录数 计算总页数)
create proc p_show(@stuName nvarchar(50),@pagesize int,@currentpage int out,@totalcount int out,@totalpage int out)
as
begin
-- 首先要对传进来的当前页进行判断,既不能小于1,也不能大于总页数
if @currentpage<1
begin
set @currentpage=1
end
-- 先计算总记录数
select @totalcount=COUNT(*) from student where stuName like ‘%‘+@stuName+‘%‘
if @totalcount % @pagesize = 0
set @totalpage=@totalcount / @pagesize
else
set @totalpage = @totalcount / @pagesize + 1
-- 在判断一下 如果当前页大于总页数,那么当前页 = 总页数
if @currentpage > @totalpage
set @currentpage = @totalpage
分页查询
select * from
(select a.id,a.stuName,a.birthDay,a.age,a.ClassId,b.ClassName,b.CCount, ROW_NUMBER() over (order by stuName)rn
from student a left join Class b on a.ClassId=b.ClassId where a.stuName like ‘%‘+@stuName+‘%‘)t1
where rn between (@currentpage-1) * @pagesize + 1 and @currentpage * @pagesize
end
go
--两个输出字段
declare @currentpage int,@totalCount int, @totalPage int
set @currentpage = 1
exec p_show ‘四‘,8,@currentpage out,@totalCount out, @totalPage out
select @currentpage,@totalCount,@totalPage
如果您也喜欢它,动动您的小指点个赞吧