SQL简单分页查询

create procedure GetDataByPage(
 @sqlStr       varchar(8000),
 @pageSize     int --页面大小
 @currentPage  int --当前页面
)

as 
  declare @firstRec  int
  declare @lastRec   int
  declare @dt        varchar(20)
  declare @tempTableName varchar(16)
  --计算页面算法
  select @firstRec = ( @currentPage - 1) * @pageSize
  select @lastRec  = ( @currentPage * @pageSize + 1)
  --页起始行,页结束行,生成临时表的随机数
  select @dt = substring( convert(varchar,rand()),3,10) 
  select @sqlStr = stuff( @sqlStr, charindex(' FROM ',upper(@sqlStr)),6,
                         ' into tempdb..Page'+ @dt +' FROM ')
  exec( @sqlStr )
  select @tempTableName = 'tempdb..Page'+ @dt 
  --增加ID号
  select @sqlStr = ' alter '+@tempTableName+' add TEMPDB_ID numberic(10) identity primary key '
  --查询
  select @sqlStr = ' select * from ' + @tempTableName 
                 + ' where  TEMPDB_ID > ' + convert( varchar,@firstRec ) 
                 + ' and TEMPDB_ID < '   + convert( varchar,@lastRec )
  
  exec(@sqlStr)
  
  --删除临时表 
  select @sqlStr = ' drop table  ' + @tempTableName
  exec(@sqlStr)