ROW_NUMBER分页 | Bruce's Blog

--提取分页数据，返回总记录数
create procedure Proc_GetDataPaging_ReturnDataCount
(
@SqlString varchar(6000), --查询语句
@PageNumber int, --当前的页码
@PageSize int, --每页显示的数据量
@SequenceField varchar(100), --排序字段
@DataCount int out --总数据量
)
as
begin
--提取总记录数
declare @Sql nvarchar(2000)
set @Sql = N'select @countNum = count(1) from ('+ @SqlString+') as a'
exec sp_executesql @Sql,N'@countNum int out',@DataCount out
declare @BNum int
declare @ENum int
set @BNum = (@PageNumber-1)*@PageSize+1
set @ENum = @PageNumber*@PageSize
set @SqlString = substring(@SqlString,charindex('select',@SqlString)+6,len(@SqlString))
set @SqlString = 'SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY '
+@SequenceField+' ) AS RowNumber ,'+@SqlString +' ) as pageinga123
WHERE RowNumber BETWEEN '+convert(varchar(10),@BNum)+' AND '
+convert(varchar(10),@ENum)+' ORDER BY RowNumber '
print @SqlString
exec(@SqlString)
end