sqlserver分页存储过程

最新推荐文章于 2023-10-08 14:37:22 发布

「已注销」

最新推荐文章于 2023-10-08 14:37:22 发布

阅读量519

点赞数

SqlServer 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用 SQL 存储过程实现分页查询的方法。该存储过程接受表名、字段、排序依据等参数，并返回指定页数的数据记录及总记录数。此方法灵活高效，适用于多种数据库环境。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--分页存储过程
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