创建分页存储过程

 创建分页存储过程
use pubs

go
if exists(select name from sysobjects where name='Title_pubs' and type='p')
drop procedure Title_pubs

go

create procedure
Title_pubs
 @pagemax int=5,--每页返回的记录数

 @pagesize int=1,--当前要求要显示的页的页码
 @cells int  --已经显示过的记录数

as
declare @sqlstr varchar(200)

if @pagesize<>1

 begin  
  
set @sqlstr='select top '+ convert(varchar,@pagemax)+' * from titles
where title_id not in(select top '+convert(varchar,@cells)+' title_id from titles order by title_id asc)order by title_id asc '
  print @sqlstr
  execute(@sqlstr)
  --select top @pagemax * from titles where title_id not in(select top @cells title_id from titles order by title_id asc) order by title_id asc
 end
else
 begin
  set @sqlstr='select top '+ convert(varchar,@pagemax)+' * from titles'
  --select top @pagemax * from titles
  execute(@sqlstr)
 end
 

execute Title_pubs @pagemax=5,@pagesize=4,@cells=15