--分页存储过程
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
