--通用的分页存储过程
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
