两种分页存储过程
1.创建测试环境
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create database DBTest
use DBTest
--创建测试表
create table pagetest
(
id int identity(1,1) not null,
col01 int null,
col02 nvarchar(50) null,
col03 datetime null
)
2.插入10000条数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
floor(rand() 一个随机函数
3.分页sql,下面例子是每页50条,198*50=9900,取第199页数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
4.ROWNUMBER的方式封装到存储过程。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE proc [dbo].[spSqlPageByRownumber]
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@PageSize int, --页尺寸
@PageIndex int, --页码
@strWhere varchar(1000), --查询条件
@StrOrder varchar(255), --排序条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strSqlCount nvarchar(500)--查询记录总数主语句
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where 1=1'+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
end
--------------分页------------
if @PageIndex <= 0
begin
set @PageIndex = 1
end
set @strSql='Select * from (Select row_number() over('+@strOrder+') rowId,'+ @tbFields
+' from ' + @tbName + ' where 1=1 ' + @strWhere+' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize)
+' and tb.rowId <= ' +str(@PageIndex*@PageSize)
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec(@strSql)
5.Max/top:(需要满足主键字段名称就是"id")
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE proc [dbo].[spSqlPageByMaxTop]
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@PageSize int, --页尺寸
@PageIndex int, --页码
@strWhere varchar(1000), --查询条件
@StrOrder varchar(255), --排序条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strSqlCount nvarchar(500)--查询记录总数主语句
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where 1=1 '+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
end
--------------分页------------
if @PageIndex <= 0
begin
set @PageIndex = 1
end
set @strSql='select top '+str(@PageSize)+' * from ' + @tbName + '
where 1=1 '+ @strWhere +' and id>(select max(id) from (select top '+str((@PageIndex-1)*@PageSize)+' id from ' + @tbName + ''+@strOrder+')a)
'+@strOrder+''
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec(@strSql)