//过程名和输入输出参数
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