sql server 存储过程分页

create proc [dbo].[sp_GetPagedList]
(
@pageIndex int,
@pageSize int,
@orderBy nvarchar(512),
@columns varchar(512) ,
@tableName varchar(512),
@where nvarchar(max)
)
AS
begin
declare @sql nvarchar(max)
declare @currPage int
set @currPage=@pageIndex*@pageSize
set @sql=‘
select count(*) from (
select ROW_NUMBER() over (order by ‘+@orderBy+‘ desc) as rowid,‘+@columns+‘ from ‘+@tableName+‘ where 1=1 ‘+
@where+‘
) temp ‘
 
exec(@sql)
set @sql=‘
select * from (
select ROW_NUMBER() over (order by ‘+@orderBy+‘ desc) as rowid,‘+@columns+‘ from ‘+@tableName+‘ where 1=1 ‘+
@where+‘
) temp where rowid between ‘+Convert(varchar(50),@currPage-@pageSize+1)+‘ and ‘+Convert(varchar(50),@currPage)
exec(@sql)
end

sql server 存储过程分页

标签：