收藏
下载资源下载资源 加入VIP,免费下载

SQL分页存储过程.docx

上传人：b****6 文档编号：6286858 上传时间：2023-01-05 格式：DOCX 页数：13 大小：20.94KB
下载 相关 举报
SQL分页存储过程.docx_第1页
第1页 / 共13页
SQL分页存储过程.docx_第2页
第2页 / 共13页
SQL分页存储过程.docx_第3页
第3页 / 共13页
SQL分页存储过程.docx_第4页
第4页 / 共13页
SQL分页存储过程.docx_第5页
第5页 / 共13页
点击查看更多>>
下载资源
资源描述

SQL分页存储过程.docx

《SQL分页存储过程.docx》由会员分享，可在线阅读，更多相关《SQL分页存储过程.docx（13页珍藏版）》请在冰豆网上搜索。

SQL分页存储过程.docx

SQL分页存储过程

--参数说明

-------------------------------------------------------------

-- 获取指定页的数据

CREATE PROCEDURE pagination

@tblName varchar（255）, -- 表名

@strGetFields varchar（1000） = '*', -- 需要返回的列

@fldName varchar（255）='', -- 排序的字段名

@PageSize int = 10, -- 页尺寸

@PageIndex int = 1, -- 页码

@doCount bit = 0, -- 返回记录总数, 非 0 值则返回

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

@strWhere varchar（1500） = '' -- 查询条件 （注意:

 不要加 where）

AS

declare @strSQL varchar（5000） -- 主语句

declare @strTmp varchar（110） -- 临时变量

declare @strOrder varchar（400） -- 排序类型

if @doCount !

= 0

begin

if @strWhere !

=''

set @strSQL = "select count（*） as Total from [" + @tblName + "] where "+@strWhere

else

set @strSQL = "select count（*） as Total from [" + @tblName + "]"

end

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。

以下的所有代码都是@doCount为0的情况

else

begin

if @OrderType !

= 0

begin

set @strTmp = "<（select min"

set @strOrder = " order by [" + @fldName +"] desc"

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

end

else

begin

set @strTmp = ">（select max"

set @strOrder = " order by [" + @fldName +"] asc"

end

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

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

end

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

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

end

end

exec （@strSQL）

GO

于是我对效率最高的select max方法用2分法进行了扩展，代码取自互联网，我修改了ASC排序时取不到值的BUG，测试结果:

存过

第2页耗时

第1000页

第10000页

第100000页

第199999页

效率排行

2分法

156ms

156ms

180ms

470ms

156ms

1*

从测试结果来看，使用2分法确实可以提高效率并使效率更为稳定，我又增加了第159999页的测试，用时仅296ms，效果相当的不错！

下面是2分法使用select max的代码，已相当完善。

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

--/*-----存储过程分页处理浪尘2008-9-1修改----------*/

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

alterPROCEDUREproc_paged_2part_selectMax

（

@tblNamenvarchar（200）,----要显示的表或多个表的连接

@fldNamenvarchar（500）='*',----要显示的字段列表

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

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

@fldSortnvarchar（200）=null,----排序字段列表或条件

@Sortbit=0,----排序方法，0为升序，1为降序（如果是多字段排列Sort指代最后一个

排序字段的排列顺序（最后一个排序字段不加排序标记）--程序传参如：

'SortAAsc,SortBDesc,SortC'）

@strConditionnvarchar（1000）=null,----查询条件,不需where

@IDnvarchar（150）,----主表的主键

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

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

@Countsint=1output----查询到的记录数

）

AS

SETNOCOUNTON

Declare@sqlTmpnvarchar（1000）----存放动态生成的SQL语句

Declare@strTmpnvarchar（1000）----存放取得查询结果总数的查询语句

Declare@strIDnvarchar（1000）----存放取得查询开头或结尾ID的查询语句

Declare@strSortTypenvarchar（10）----数据排序规则A

Declare@strFSortTypenvarchar（10）----数据排序规则B

Declare@SqlSelectnvarchar（50）----对含有DISTINCT的查询进行SQL构造

Declare@SqlCountsnvarchar（50）----对含有DISTINCT的总数查询进行SQL构造

declare@timediffdatetime--耗时测试时间差

select@timediff=getdate（）

if@Dist=0

begin

set@SqlSelect='select'

set@SqlCounts='Count（*）'

end

else

begin

set@SqlSelect='selectdistinct'

set@SqlCounts='Count（DISTINCT'+@ID+'）'

end

if@Sort=0

begin

set@strFSortType='ASC'

set@strSortType='DESC'

end

else

begin

set@strFSortType='DESC'

set@strSortType='ASC'

end

--------生成查询语句--------

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

if@strConditionisnullor@strCondition=''--没有设置显示条件

begin

set@sqlTmp=@fldName+'From'+@tblName

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

set@strID='From'+@tblName

end

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

end

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

execsp_executesql@strTmp,N'@Countsintout',@Countsout

declare@tmpCountsint

if@Counts=0

set@tmpCounts=1

else

set@tmpCounts=@Counts

--取得分页总数

set@pageCount=（@tmpCounts+@pageSize-1）/@pageSize

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

if@page>@pageCount

set@page=@pageCount

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

declare@pageIndexint--总数/页大小

declare@lastcountint--总数%页大小

set@pageIndex=@tmpCounts/@pageSize

set@lastcount=@tmpCounts%@pageSize

if@lastcount>0

set@pageIndex=@pageIndex+1

else

set@lastcount=@pagesize

--//***显示分页

if@strConditionisnullor@strCondition=''--没有设置显示条件

begin

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

begin

if@page=1

set@strTmp=@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''+@fldName+'from'+@tblName

+'orderby'+@fldSort+''+@strFSortType

else

begin

if@Sort=1

begin

set@strTmp=@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''+@fldName+'from'+@tblName

+'where'+@ID+'<（selectmin（'+@ID+'）from（'+@SqlSelect+'top'

+CAST（@pageSize*（@page-1）asVarchar（20））+''+@ID+'from'+@tblName

+'orderby'+@fldSort+''+@strFSortType+'）ASTBMinID）'

+'orderby'+@fldSort+''+@strFSortType

end

else

begin

set@strTmp=@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''+@fldName+'from'+@tblName

+'where'+@ID+'>（selectmax（'+@ID+'）from（'+@SqlSelect+'top'

+CAST（@pageSize*（@page-1）asVarchar（20））+''+@ID+'from'+@tblName

+'orderby'+@fldSort+''+@strFSortType+'）ASTBMinID）'

+'orderby'+@fldSort+''+@strFSortType

end

end

end

else

begin

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

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

set@strTmp=@SqlSelect+'*from（'+@SqlSelect+'top'

+CAST（@lastcountasVARCHAR（4））+''+@fldName+'from'+@tblName

+'orderby'+@fldSort+''+@strSortType+'）ASTempTB'+'orderby'

+@fldSort+''+@strFSortType

else

if@Sort=1

begin

set@strTmp=@SqlSelect+'*from（'+@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''

+@fldName+'from'+@tblName

+'where'+@ID+'>（selectmax（'+@ID+'）from（'+@SqlSelect+'top'

+CAST（@pageSize*（@page-2）+@lastcountasVarchar（20））+''+@ID+'from'+@tblName

+'orderby'+@fldSort+''+@strSortType+'）ASTBMaxID）'

+'orderby'+@fldSort+''+@strSortType+'）ASTempTB'+'orderby'+@fldSort+''

+@strFSortType

end

else

begin

set@strTmp=@SqlSelect+'*from（'+@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））

+''+@fldName+'from'+@tblName

+'where'+@ID+'<（selectmin（'+@ID+'）from（'+@SqlSelect+'top'

+CAST（@pageSize*（@page-2）+@lastcountasVarchar（20））+''+@ID+'from'+@tblName

+'orderby'+@fldSort+''+@strSortType+'）ASTBMaxID）'

+'orderby'+@fldSort+''+@strSortType+'）ASTempTB'+'orderby'+@fldSort+''

+@strFSortType

end

end

end

else--有查询条件

begin

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

begin

if@page=1

set@strTmp=@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''+@fldName+'from'+@tblName

+'where1=1'+@strCondition+'orderby'+@fldSort+''+@strFSortType

elseif（@Sort=1）

begin

set@strTmp=@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''+@fldName+'from'+@tblName

+'where'+@ID+'<（selectmin（'+@ID+'）from（'+@SqlSelect+'top'

+CAST（@pageSize*（@page-1）asVarchar（20））+''+@ID+'from'+@tblName

+'where（1=1）'+@strCondition+'orderby'+@fldSort+''+@strFSortType+'）ASTBMinID）'

+''+@strCondition+'orderby'+@fldSort+''+@strFSortType

end

else

begin

set@strTmp=@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''+@fldName+'from'+@tblName

+'where'+@ID+'>（selectmax（'+@ID+'）from（'+@SqlSelect+'top'

+CAST（@pageSize*（@page-1）asVarchar（20））+''+@ID+'from'+@tblName

+'where（1=1）'+@strCondition+'orderby'+@fldSort+''+@strFSortType+'）ASTBMinID）'

+''+@strCondition+'orderby'+@fldSort+''+@strFSortType

end

end

else

begin

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

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

set@strTmp=@SqlSelect+'*from（'+@SqlSelect+'top'

+CAST（@lastcountasVARCHAR（4））+''+@fldName+'from'+@tblName

+'where（1=1）'+@strCondition+'orderby'+@fldSort+''+@strSortType+'）ASTempTB'

+'orderby'+@fldSort+''+@strFSortType

elseif（@Sort=1）

set@strTmp=@SqlSelect+'*from（'+@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''

+@fldName+'from'+@tblName

+'where'+@ID+'>（selectmax（'+@ID+'）from（'+@SqlSelect+'top'+CAST（@pageSize*（@page-2）

+@lastcountasVarchar（20））+''+@ID+'from'+@tblName

+'where（1=1）'+@strCondition+'orderby'+@fldSort+''+@strSortType+'）ASTBMaxID）'

+''+@strCondition+'orderby'+@fldSort+''+@strSortType+'）ASTempTB'+'orderby'

+@fldSort+''+@strFSortType

else

set@strTmp=@SqlSelect+'*from（'+@SqlSelect+'top'+CAST（@pageSizeasVARCHAR（4））+''

+@fldName+'from'+@tblName

+'where'+@ID+'<（selectmin（'+@ID+'）from（'+@SqlSelect+'top'

+CAST（@pageSize*（@page-2）+@lastcountasVarchar（20））+''+@ID+'from'+@tblName

+'where（1=1）'+@strCondition+'orderby'+@fldSort+''+@strSortType+'）ASTBMaxID）'

+''+@strCondition+'orderby'+@fldSort+''+@strSortType+'）ASTempTB'+'orderby'

+@fldSort+''+@strFSortType

end

end

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

execsp_executesql@strTmp

selectdatediff（ms,@timediff,getdate（））as耗时

--print@strTmp

SETNOCOUNTOFF

GO

执行示例：

execproc_paged_2part_selectMax'tb_testTable','ID,userName,userPWD,

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置：首页 > 表格模板 > 合同协议

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1