sqlserver分页存储过程

--分页存储过程
if (OBJECT_ID('proc_page','P') is not null)
drop proc proc_page
go
create proc proc_page
(
@tabName varchar(2000),
@fieldStr varchar(2000) = '*',
@orderStr varchar(2000),
@whereStr varchar(2000) = N'',
@pageSize int,
@pageIndex int,
@totalNum int output
)
as
begin
declare @startNum int,@endNum int,@totalCountSql nvarchar(500),@sqlStr varchar(5000);
set @startNum = (@pageIndex -1) * @pageSize +1
set @endNum = @startNum + @pageSize -1
set @totalCountSql = N'select @totalNum = count(*) from ' + @tabName;
set @sqlStr = N'(select row_number() over (order by ' + @orderStr +') as rowId,' + @fieldStr + ' from ' + @tabName + ' as h';

if(@whereStr !='' or @whereStr != null)
begin
set @totalCountSql = @totalCountSql + ' where '+ @whereStr
set @sqlStr = @sqlStr + ' where '+ @whereStr
end
exec sp_executesql @totalCountSql,N'@totalNum int out',@totalNum output;
set @sqlStr = N'select * from ' + @sqlStr + ') as t where rowId between ' + ltrim(str(@startNum)) + ' and ' +  ltrim(str(@endNum));
exec(@sqlStr)
print(@sqlStr)
end

--调用
declare @totalCount int
exec dbo.proc_page 'dbo.QM_Project_BasicInfo','*','prjId','',10,1,@totalCount output
select @totalCount as totalcount