存储过程分页

--用户表
create proc SP_Tb_UserInfo_Page
@pageIndex int,--第几页
@pageSize int,--一页的个数
@pageCount int output--页的个数
as
begin
    declare @Count int 
    select @Count=count(UserId) from Tb_UserInfo
    if(@Count%@pageSize>0)
    set @pageCount=(@Count/@pageSize)+1
    else
    set @pageCount=@Count/@pageSize    
    declare @sql varchar(8000)
    set @sql='select top '+str(@pageSize)+' * from Tb_UserInfo where UserId not in (select top'+str((@pageIndex-1)*@pageSize)+' UserId from Tb_UserInfo order by UserId )'
    exec(@sql)
    
end

declare  @pageCount int
exec SP_Tb_UserInfo_Page 1,3 ,@pageCount output
select @pageCount


  --商品表
create proc SP_Tb_Product_Page
@pageIndex int,--第几页
@pageSize int,--一页的个数
@pageCount int output--页的个数
as
begin
    declare @Count int 
    select @Count=count(ProductID) from Tb_Product
    if(@Count%@pageSize>0)
    set @pageCount=(@Count/@pageSize)+1
    else
    set @pageCount=@Count/@pageSize    
    declare @sql varchar(8000)
    set @sql='select top '+str(@pageSize)+' * from Tb_Product where ProductID not in (select top'+str((@pageIndex-1)*@pageSize)+' ProductID from Tb_Product order by ProductID )'
    exec(@sql)
    
end

declare  @pageCount int
exec SP_Tb_Product_Page 1,3 ,@pageCount output
select @pageCount


--订单表

create proc SP_TB_Order_Page
@pageIndex int,--第几页
@pageSize int,--一页的个数
@pageCount int output--页的个数
as
begin
    declare @Count int 
    select @Count=count(OrderID) from TB_Order
    if(@Count%@pageSize>0)
    set @pageCount=(@Count/@pageSize)+1
    else
    set @pageCount=@Count/@pageSize    
    declare @sql varchar(8000)
    set @sql='select top '+str(@pageSize)+' * from TB_Order where OrderID not in (select top'+str((@pageIndex-1)*@pageSize)+' OrderID from TB_Order order by OrderID )'
    exec(@sql)
    
end

declare  @pageCount int
exec SP_TB_Order_Page 1,3 ,@pageCount output
select @pageCount

--留言表
create proc SP_Tb_Leave_Page
@pageIndex int,--第几页
@pageSize int,--一页的个数
@pageCount int output--页的个数
as
begin
    declare @Count int 
    select @Count=count(LeaveId) from Tb_Leave
    if(@Count%@pageSize>0)
    set @pageCount=(@Count/@pageSize)+1
    else
    set @pageCount=@Count/@pageSize    
    declare @sql varchar(8000)
    set @sql='select top '+str(@pageSize)+' * from Tb_Leave where LeaveId not in (select top'+str((@pageIndex-1)*@pageSize)+' LeaveId from Tb_Leave order by LeaveId )'
    exec(@sql)
    
end

declare  @pageCount int
exec SP_Tb_Leave_Page 1,3 ,@pageCount output
select @pageCount


--回复表

create proc SP_Tb_Restore_Page
@pageIndex int,--第几页
@pageSize int,--一页的个数
@pageCount int output--页的个数
as
begin
    declare @Count int 
    select @Count=count(RestoreID) from Tb_Restore
    if(@Count%@pageSize>0)
    set @pageCount=(@Count/@pageSize)+1
    else
    set @pageCount=@Count/@pageSize    
    declare @sql varchar(8000)
    set @sql='select top '+str(@pageSize)+' * from Tb_Restore where RestoreID not in (select top'+str((@pageIndex-1)*@pageSize)+' RestoreID from Tb_Restore order by RestoreID )'
    exec(@sql)
    
end

declare  @pageCount int
exec SP_Tb_Restore_Page 1,3 ,@pageCount output
select @pageCount


 --消息表
create proc SP_Tb_News_Page
@pageIndex int,--第几页
@pageSize int,--一页的个数
@pageCount int output--页的个数
as
begin
    declare @Count int 
    select @Count=count(NewsID) from Tb_News
    if(@Count%@pageSize>0)
    set @pageCount=(@Count/@pageSize)+1
    else
    set @pageCount=@Count/@pageSize    
    declare @sql varchar(8000)
    set @sql='select top '+str(@pageSize)+' * from Tb_News where NewsID not in (select top'+str((@pageIndex-1)*@pageSize)+' NewsID from Tb_News order by NewsID )'
    exec(@sql)
    
end

declare  @pageCount int
exec SP_Tb_News_Page 1,3 ,@pageCount output
select @pageCount