ROW_NUMBER存储过程分页

--提取分页数据，返回总记录数
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 RowNumber121 ,'+@SqlString +' ) as pageinga123 WHERE RowNumber121 BETWEEN  '+convert(varchar(10),@BNum)+' AND '+convert(varchar(10),@ENum)+' ORDER BY RowNumber121 '
print @SqlString
exec(@SqlString)

end

转载于:https://www.cnblogs.com/zhang9418hn/archive/2011/11/07/2239604.html