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)

转载于:https://www.cnblogs.com/pengshaomin/archive/2012/03/19/2407168.html