create procedure proc_paged_with_Midvar --利用ID>最大ID值和中间变量
(
@pageIndex int,
@pageSize int
)
as
declare @count int
declare @ID int
declare @timediff datetime
declare @sql nvarchar(500)
begin
set nocount on;
select @count=0,@ID=0 ,@timediff=getdate()
select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then menuId else @ID end from menus
print @count;
set @sql='select top '+str(@pageSize)+' * from menus where menuId>'+str(@ID)
execute(@sql)
select datediff(ms,@timediff,getdate()) as 耗时
set nocount off;
end
--调用存储过程
exec dbo.proc_paged_with_Midvar 1,2
(
@pageIndex int,
@pageSize int
)
as
declare @count int
declare @ID int
declare @timediff datetime
declare @sql nvarchar(500)
begin
set nocount on;
select @count=0,@ID=0 ,@timediff=getdate()
select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then menuId else @ID end from menus
print @count;
set @sql='select top '+str(@pageSize)+' * from menus where menuId>'+str(@ID)
execute(@sql)
select datediff(ms,@timediff,getdate()) as 耗时
set nocount off;
end
--调用存储过程
exec dbo.proc_paged_with_Midvar 1,2