能处理百万数据的存储过程

//过程名和输入输出参数

CREATE PROCEDURE GetCustomDataPage @pageSize int, @pageIndex int, @pageCount int output, @recordCount int output AS

//定义变量

declare @SQL varchar(1000)

//取得记录数量

select @recordCount=count(*) from products

//计算得到页数

set @pageCount=ceiling(@recordCount*1.0/@pageSize)

//经典算法,我还没有看明白

if @pageIndex = 0 or @pageCount<=1

 set @SQL='select top '+str(@pageSize)+' productID,productName, unitPrice from products order by productID asc'

else if @pageIndex = @pageCount -1

 set @SQL='select * from ( select top '+str(@recordCount - @pageSize * @pageIndex)+' productID,productName, unitPrice from products order by productID desc) TempTable order by productID asc'

else set @SQL='select top '+str(@pageSize) +' * from ( select top '+str(@recordCount - @pageSize * @pageIndex)+' productID,productName, unitPrice from products order by productID desc) TempTable order by productID asc' exec(@SQL)

GO