例如查询AdventureWorks数据库中Production.Product记录

一次全部显示，数据量太大，希望每次显示10条数据，按页来显示

显示第5页的数据

select top 10 * from Production.Product

where ProductID not in (

select top (10 * (5-1)) ProductID

from Production.Product

order by ProductID

)

order by ProductID

将上面查询语句改写成存储过程

create procedure page

@pageSize int, @pageNumber int , @tableName varchar(20) , @tableKeyID varchar(20)

as

begin

declare @sql varchar(8000)

set @sql = 'select top '+ cast(@pageSize as varchar(20))+' * from '+@tableName

set @sql = @sql + ' where '+ @tableKeyID +' not in ('

set @sql = @sql + 'select top ('+cast(@pageSize as varchar(20))+' * ('+cast (@pageNumber as varchar(20))+'-1)) '+ @tableKeyID

set @sql = @sql + ' from '+@tableName

set @sql = @sql + ' order by '+ @tableKeyID

set @sql = @sql + ')order by '+ @tableKeyID

--print @sql

exec(@sql)

end

调用

exec page 2,20 ,'Production.Product','ProductID'

exec page 10 ,3 ,'Person.Contact','ContactID'