利用存储过程实现数据分页

利用MS-SQL2000的存储过程实现数据分页读取，可简化读取的数据量。本过程的优点在于，输入的SQL语句没有限制条件，而且写法和思路都很简单明了。

CREATE PROCEDURE [dbo].[PageShow]
  @sql varchar(8000), --where语句
  @PageSize int=10,   --每页的大小(行数)
  @PageCurrent int=1   --要显示的页
AS
set @sql=ltrim(rtrim(lower(@sql)))
 
set @sql=left(@sql,7)+' top '+cast(@PageSize*@PageCurrent as varchar(100))+' '+ right(@sql,len(@sql)-7)
set @sql='select identity(int,0,1) as PageShowID,* into #temp1 from ('+@sql+') a'
 set @sql=@sql+'  select *  from #temp1 where PageShowID between '+cast(@PageSize*(@PageCurrent-1) as varchar(100))+' and ' +cast(@PageSize*@PageCurrent as varchar(100))
set @sql=@sql+' drop table #temp1'
 exec (@sql)
GO

 

 

 实例执行：

  exec PageShow  'SELECT * FROM Customers WHERE (Country = '''Germany''') ORDER BY CustomerID',10,2

  exec PageShow  'SELECT * FROM  (select distict country from Customers WHERE (Country = '''Germany''') ORDER BY CustomerID) a',10,2