SQL Server高性能分页

ALTER PROCEDURE [dbo].[UP_GetRecordsByPage]
@tblName varchar(2000), -- 表名
@selFieldList varchar(5000), --选取字段列表   示例：a,b 支持*
@sortFieldList varchar(2000), -- 排序字段列表  示例：a desc,b asc
@PageSize int = 1, -- 页尺寸
@PageIndex int = 1, -- 页码
@strWhere varchar(8000), -- 查询条件 (注意: 不要加 where)
@count varchar(20) output
AS

declare @strSQL varchar(8000),@sq nvarchar(500),@num varchar(20) -- 主语句

set @strSQL='select * from (select ROW_NUMBER() OVER(ORDER BY '+@sortFieldList+') as rowNum ,'+@selFieldList+' from '+@tblName


set @sq='select @count=count(*) from '+@tblName
if(RTRIM(@strWhere)<>'')
begin
set @sq=@sq+' where '+@strWhere
set @strSQL=@strSQL+' where '+@strWhere
end
set @strSQL=@strSQL+')_R where rowNum>'+convert(varchar(10),(@PageIndex-1)*@PageSize)+' and rowNum<='+convert(varchar(10),@PageSize*@PageIndex)

execute sp_executesql 
@sq,
  N'@count int output',
  @count output
exec (@strSQL)


GO

转载于:https://www.cnblogs.com/whz881027/articles/2103275.html