SqlServer中使用row_number() over实现通用的分页存储过程 - daisy_thq - 博客园

来源： 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