来源: SqlServer中使用row_number() over实现通用的分页存储过程 – daisy_thq – 博客园
–通用的分页存储过程
create procedure sp_pager
(
@SQL nvarchar(4000), –要分页的SQL语句
@CurrentPageNo int, –当前页面索引
@PageSize int, –每一页要显示的页数
@TotalNum int output –数据的总条数 (输出参数)
)
as
declare @sqlcmd varchar(8000)
–查询数据
set @sqlcmd = ‘select * from (‘ + @Sql + ‘) a where RowIndex between ‘ + convert(nvarchar,(@CurrentPageNo-1) * @PageSize + 1) + ‘ and ‘ + convert(varchar,@CurrentPageNo * @PageSize)
exec(@sqlcmd)
print (@sqlCmd)
–求记录总数
create table tempTable(num int)
insert into tempTable exec(‘select count(*) from (‘ + @Sql + ‘) a’)
select @TotalNum=(select * from tempTable)
drop table tempTable
go
–=========================================测试存储过程
declare @Sql varchar(5000)
declare @CurrentPageNo int
declare @PageSize int
declare @TotalNum int
set @CurrentPageNo = 2
set @PageSize = 4
set @Sql=’ select products.PID,products.PName,products.MarketPrice,productDispose.ShopPrice,row_number() over (order by products.PID) as RowIndex from ProductsDisposeInfo productDispose inner join ProductsInfo products on productDispose.PID=products.PID’
exec sp_pager @Sql,@CurrentPageNo,@PageSize,@TotalNum output
print @TotalNum