create procedure p @pageIndex int, --以1开始 @pageSize int --每页大小 as declare @tb table ( num int identity, id int, name varchar(20) )insert @tb(id,name) select * from a order by idset rowcount @pageSize --选取指定数量记录select id,name from @tb where num>(@pageIndex-1)*@pagesizeset rowcount 0go
--借用vivianfdlpw() 的思路改动如下: create procedure p_1 @currentpage int,--当前页 @allPage int--总页数 as declare @page int--每页的记录数 declare @total int --总记录数 declare @tb table ( num int identity, id int, name varchar(20) ) select @total=count(*) from tb set @page=@total/@allpage if @total>@page*@allpage set @page=@page+1insert @tb(id,name) select * from tb order by [id]set rowcount @page --选取指定数量记录select id,name from @tb where num>(@currentpage-1)*@pageset rowcount 0go
create procedure XiaoZhengGe @sqlstr nvarchar(4000), --查询字符串 @currentpage int, --第N页 @pagesize int --每页行数 as set nocount on declare @P1 int, --P1是游标的id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 set @currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off
@pageIndex int, --以1开始
@pageSize int --每页大小
as
declare @tb table
(
num int identity,
id int,
name varchar(20)
)insert @tb(id,name)
select * from a order by idset rowcount @pageSize --选取指定数量记录select id,name
from @tb
where num>(@pageIndex-1)*@pagesizeset rowcount 0go
create procedure p_1
@currentpage int,--当前页
@allPage int--总页数
as
declare @page int--每页的记录数
declare @total int --总记录数
declare @tb table
(
num int identity,
id int,
name varchar(20)
)
select @total=count(*) from tb
set @page=@total/@allpage
if @total>@page*@allpage set @page=@page+1insert @tb(id,name)
select * from tb order by [id]set rowcount @page --选取指定数量记录select id,name
from @tb
where num>(@currentpage-1)*@pageset rowcount 0go
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off