您现在的位置是：首页 > 后端

当前栏目

Sql Server 分页存储过程

server 存储 SQL 过程分页

2023-09-14 09:03:14 时间

--/*-----存储过程分页处理孙伟 2005-03-28创建 -------*/ --/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/ --/*-----存储过程分页处理孙伟 2005-04-21修改添加Distinct查询功能-------*/ --/*-----存储过程分页处理孙伟 20

--/*-----存储过程 分页处理 孙伟 2005-03-28创建 -------*/ 

--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/ 

--/*-----存储过程 分页处理 孙伟 2005-04-21修改 添加Distinct查询功能-------*/ 

--/*-----存储过程 分页处理 孙伟 2005-05-18修改 多字段排序规则问题-------*/ 

--/*-----存储过程 分页处理 孙伟 2005-06-15修改 多字段排序修改-------*/ 

--/*-----存储过程 分页处理 孙伟 2005-12-13修改 修改数据分页方式为top max模式性能有极大提高-------*/ 

--/*-----缺点：相对之前的not in版本主键只能是整型字段,如主键为GUID类型请使用not in 模式的版本-------*/ 

CREATE PROCEDURE dbo.proc_ListPageInt 

@tblName nvarchar(200), ----要显示的表或多个表的连接 

@fldName nvarchar(500) = *, ----要显示的字段列表 

@pageSize int = 10, ----每页显示的记录个数 

@page int = 1, ----要显示那一页的记录 

@pageCount int = 1 output, ----查询结果分页后的总页数 

@Counts int = 1 output, ----查询到的记录数 

@fldSort nvarchar(200) = null, ----排序字段列表或条件 

@Sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如： SortA Asc,SortB Desc,SortC ) 

@strCondition nvarchar(1000) = null, ----查询条件,不需where 

@ID nvarchar(150), ----主表的主键 

@Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 

SET NOCOUNT ON 

Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句 

Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句 

Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句 

Declare @strSortType nvarchar(10) ----数据排序规则A 

Declare @strFSortType nvarchar(10) ----数据排序规则B 

Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造 

Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造 


--此处@strTmp为取得查询结果数量的语句 

if @strCondition is null or @strCondition= --没有设置显示条件 

begin 

set @sqlTmp = @fldName +  From  + @tblName 

set @strTmp = @SqlSelect+ @Counts=+@SqlCounts+ FROM +@tblName

set @strID =  From  + @tblName 

else 

begin 

set @sqlTmp = + @fldName + From  + @tblName +  where (1 0)  + @strCondition 

set @strTmp = @SqlSelect+ @Counts=+@SqlCounts+ FROM +@tblName +  where (1 0)  + @strCondition 

set @strID =  From  + @tblName +  where (1 0)  + @strCondition 

----取得查询结果总数量----- 

exec sp_executesql @strTmp,N@Counts int out ,@Counts out 

declare @tmpCounts int 

if @Counts = 0 

set @tmpCounts = 1 

else 

set @tmpCounts = @Counts 

--取得分页总数 

set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize 

/**//**当前页大于总页数 取最后一页**/ 

if @page @pageCount 

set @page=@pageCount 

--/*-----数据分页2分处理-------*/ 

declare @pageIndex int --总数/页大小 

declare @lastcount int --总数%页大小 

set @pageIndex = @tmpCounts/@pageSize 

set @lastcount = @tmpCounts%@pageSize 

if @lastcount 0 

set @pageIndex = @pageIndex + 1 

else 

set @lastcount = @pagesize 

--//***显示分页 

if @strCondition is null or @strCondition= --没有设置显示条件 

begin 

if @pageIndex 2 or @page =@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 

begin 

if @page=1 

set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName

+ order by + @fldSort + + @strFSortType 

else 

begin 

set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName

+ where +@ID+ (select min(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(@page-1) as Varchar(20)) + + @ID + from +@tblName

+ order by + @fldSort + + @strFSortType+) AS TBMinID) 

+ order by + @fldSort + + @strFSortType 

else 

begin 

set @page = @pageIndex-@page+1 --后半部分数据处理 

if @page = 1 --最后一页数据显示 

set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@lastcount as VARCHAR(4))+ + @fldName+ from +@tblName

+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType 

else 

set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName

+ where +@ID+ (select max(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) + + @ID + from +@tblName

+ order by + @fldSort + + @strSortType+) AS TBMaxID) 

+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType 

else --有查询条件 

begin 

if @pageIndex 2 or @page =@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 

begin 

if @page=1 

set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName

+ where 1=1  + @strCondition +  order by + @fldSort + + @strFSortType 

else 

begin 

set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName

+ where +@ID+ (select min(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(@page-1) as Varchar(20)) + + @ID + from +@tblName

+ where (1=1)  + @strCondition + order by + @fldSort + + @strFSortType+) AS TBMinID) 

+ + @strCondition + order by + @fldSort + + @strFSortType 

else 

begin 

set @page = @pageIndex-@page+1 --后半部分数据处理 

if @page = 1 --最后一页数据显示 

set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@lastcount as VARCHAR(4))+ + @fldName+ from +@tblName

+ where (1=1) + @strCondition + order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType 

else 

set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR(4))+ + @fldName+ from +@tblName

+ where +@ID+ (select max(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) + + @ID + from +@tblName

+ where (1=1) + @strCondition + order by + @fldSort + + @strSortType+) AS TBMaxID) 

+ + @strCondition+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType 

------返回查询结果----- 

exec sp_executesql @strTmp 

--print @strTmp 

SET NOCOUNT OFF 

GO

怎么在数据库中测试呢？

declare @pageCount int 

declare @Counts int 

exec [dbo].[proc_ListPageInt] sysobjects, * , 20,1,@pageCount output,@Counts output,id, 0,,id,0

print @pageCount --这个可有可无 

print @Counts --这个可有可无

执行效果如下：

分页存储过程二：

USE [JianKunKingTestDatabase001] 

/****** Object: StoredProcedure [dbo].[A_P_HelpPageShow] Script Date: 01/21/2015 19:19:42 ******/ 

SET ANSI_NULLS ON 

SET QUOTED_IDENTIFIER ON 


@strGetFields varchar(1000) = *, -- 需要返回的列 

@fldName varchar(255)=, -- 排序的字段名 

@PageSize int = 10, -- 页尺寸 

@PageIndex int = 1, -- 页码 

@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 

@strWhere varchar(1500) = , -- 查询条件 (注意: 不要加 where) 

@Counts int = 0 output --查询到的记录数 

declare @strSQL nvarchar(4000) -- 主语句 

declare @strTmp nvarchar(110) -- 临时变量 

declare @strOrder nvarchar(400) -- 排序类型 

declare @totalRecord int --查询到的记录数 

declare @SqlCounts nvarchar(max) ----对总数查询进行SQL构造 

--计算总记录数 

begin 

if @strWhere != 

set @SqlCounts = select @totalRecord=count(*) from  + @tblName +  where +@strWhere

else 

set @SqlCounts = select @totalRecord=count(*) from  + @tblName +  

--print @strWhere 

--print @SqlCounts 

exec sp_executesql @SqlCounts,N@totalRecord int OUTPUT,@totalRecord OUTPUT--计算总记录数 

set @Counts=@totalRecord 

begin 

if @OrderType != 0 

begin 

set @strTmp =  (select min 

set @strOrder =  order by  + @fldName + desc 

--如果@OrderType不是0，就执行降序，这句很重要！ 

else 

begin 

set @strTmp =  (select max 

set @strOrder =  order by  + @fldName + asc 

--print @strOrder 

if @PageIndex = 1 

begin 

if @strWhere !=  

set @strSQL = select top  + str(@PageSize) + +@strGetFields+  from  + @tblName +  where  + @strWhere +   + @strOrder 

else 

set @strSQL = select top  + str(@PageSize) + +@strGetFields+  from + @tblName +  + @strOrder 

--如果是第一页就执行以上代码，这样会加快执行速度 

else 

begin 

--以下代码赋予了@strSQL以真正执行的SQL代码 

set @strSQL = select top  + str(@PageSize) + +@strGetFields+  from  

+ @tblName +  where  + @fldName +   + @strTmp + (+ @fldName + ) from (select top  + str((@PageIndex-1)*@PageSize) +  + @fldName +  from  + @tblName +   + @strOrder + ) as tblTmp)+ @strOrder 

--print @strSQL 

if @strWhere !=  

set @strSQL = select top  + str(@PageSize) + +@strGetFields+  from  

+ @tblName +  where  + @fldName +   + @strTmp + ( 

+ @fldName + ) from (select top  + str((@PageIndex-1)*@PageSize) +   

+ @fldName +  from  + @tblName +  where  + @strWhere +   

+ @strOrder + ) as tblTmp) and  + @strWhere +   + @strOrder 

--print @strSQL 

exec sp_executesql @strSQL 

GO

怎么在数据库中测试呢？

select count(*) from sysobjects ;

declare @CountsAA int 

exec [dbo].[A_P_HelpPageShow] sysobjects, * , id ,20, 1,1,  ,@CountsAA output 

print @CountsAA --这个可有可无

执行结果如下：

存储过程二（优化版）

USE [JianKunKingTestDatabase001]

/****** Object: StoredProcedure [dbo].[A_P_HelpPageShow] Script Date: 01/30/2015 20:21:13 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON


@strGetFields varchar(1000) = *, -- 需要返回的列 

@OrderField varchar(255)=, -- 排序的字段名

@PageSize int = 10, -- 页尺寸

@PageIndex int = 1, -- 页码

@strWhere varchar(1500) = , -- 查询条件 (注意: 不要加 where)

@Counts int = 0 output --查询到的记录数

declare @strSQL nvarchar(4000) -- 主语句

declare @totalRecord int --查询到的记录数

declare @SqlCounts nvarchar(max) ----对总数查询进行SQL构造

--计算总记录数

begin

 if @strWhere !=

set @SqlCounts = select @totalRecord=count(*) from  + @TableName +  where +@strWhere

 else

set @SqlCounts = select @totalRecord=count(*) from  + @TableName +  

exec sp_executesql @SqlCounts,N@totalRecord int OUTPUT,@totalRecord OUTPUT--计算总记录数

set @Counts=@totalRecord 

BEGIN

IF (@strWhere= or @strWhere IS NULL)

SET @strSQL = Select * FROM (select  + @strGetFields + ,ROW_NUMBER() Over(order by  + @OrderField + ) as rowId from  + @TableName

SET @strSQL = Select * FROM (select  + @strGetFields + ,ROW_NUMBER() Over(order by  + @OrderField + ) as rowId from  + @TableName +  where  + @strWhere 

 --处理页数超出范围情况 

 IF @PageIndex =0 

 SET @PageIndex = 1

 --处理开始点和结束点

 DECLARE @StartRecord INT 

 DECLARE @EndRecord int

 SET @StartRecord = (@pageIndex-1)*@PageSize + 1

 SET @EndRecord = @StartRecord + @PageSize - 1

 --继续合成sql语句

 SET @strSQL = @strSQL + ) as tempTable where rowId = + CONVERT(VARCHAR(50),@StartRecord) +  and rowid =  + CONVERT(VARCHAR(50),@EndRecord)

exec sp_executesql @strSQL


  【云原生】SQL（及存储过程）跑得太慢怎么办？ 

 SQL作为目前最常用的数据处理语言，广泛应用于查询、跑批等场景。当数据量较大时，使用SQL（以及存储过程）经常会发生跑得很慢的情况，这就要去优化SQL。


  【Sql Server】存储过程通过定时执行添加记录作业 

 通过上篇了解了什么是存储过程，创建存储过程的方法，以及调用存储过程的方法 

本次将通过数据库中的作业功能，进行定时执行存储过程，这样就可以完成我们刚开始假设的场景


  【Sql Server】存储过程通过作业定时执行按天统计记录 

 通过前两篇文章的学习，我们已经对创建表、存储过程、作业等功能点有所了解

本次将结合前面所学习的知识点，创建统计表以及结合作业定时按天以及实时统计域名各个长度的记录值


  【Sql Server】存储过程的创建和调用，随机添加域名记录 

 假设有这样一个场景 创建一个储存过程A，它执行添加一条随机产生3到8位长度的域名记录，通过定时器T1每隔1秒执行一次存储过程A 创建另一个存储过程B，它执行统计域名的长度3到8的记录数，通过定时器T2每隔1秒执行一次存储过程B


  RDS for SQL Serve使用OSS备份文件增量上云示例 

 今天这篇文章主要给大家分享基于OSS的增量上云到RDS SQL Server的这样一个功能。这个功能主要适用于以下三个场景：

一、用户希望基于备份文件物理迁移上云RDS SQL Server，而不是逻辑迁移。

猜你喜欢

ABAP 批导套用模板详解编程语言
处理解决Java中Redis过期数据处理问题（redisjava过期）
用Redis进行远程批量删除的简单方法（redis远程批量删除）
Flyme 4.2.2.1 For MX4稳定版固件发布
加速项目发展探索Redis的秘密（在项目中添加redis）
利用Metasploit库中的Oracle脚本攻击数据库（msf oracle）
与中国邮政合作的无人机物流公司：卖服务比卖设备更有价值
JQuery前台切换网站的样式实现
Linux发展前进的新分支：探索未知的美好（linux的分支）
go-接口的嵌套和类型断言（一）
逆向 EVM - 解析原始Calldata数据
MySQL服务器地址: 找到完美的解决方案（mysql服务器的地址）
生成数字Oracle中实现不重复随机数字生成（oracle随机不重复）
ECCV 2022 | CMU提出首个快速知识蒸馏的视觉框架：ResNet50 80.1%精度，训练加速30%
优化数据管理：MySQL成为数据仓库工具首选（mysql数据仓库工具）

相关主题

live server
SQL Server时间戳
PHP $_SERVER 详解
sql server 2016
sql server 笔记

zl程序教程

当前栏目

Sql Server 分页存储过程

相关文章