Version1.0.52014.07.15更新 本存储过程是本人查找对比网络上常用的分页存储过程后改写的分页存储过程,拥有较高效率,但不保证效率最高。 本存储过程是在MSSQL2012上编写,不保证兼容所有版本MSSQL(已知MSSQL2005需要修改少量代码)。不兼容其他数据库。 本
Version 1.0.5 2014.07.15更新
本存储过程是本人查找对比网络上常用的分页存储过程后改写的分页存储过程,拥有较高效率,但不保证效率最高。
本存储过程是在MSSQL2012上编写,不保证兼容所有版本MSSQL(已知MSSQL2005需要修改少量代码)。不兼容其他数据库。
本分页存储过程仅支持常用SQL语句。
若发现问题,请联系[email protected]或到我的个人博客http://blog.ttionya.com留言,共同进步!!
一些注意事项见文件内的README <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms -ecms
- –> <!—ecms -ecms
- $velocityCount
–> <!—ecms -ecms
–>
?SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*READMEVersion 1.0.5本存储过程是本人查找对比网络上常用的分页存储过程后改写的分页存储过程,拥有较高效率,但不保证效率最高。本存储过程是在MSSQL2012上编写,不保证兼容所有版本MSSQL(已知MSSQL2005需要修改少量代码)。不兼容其他数据库。本分页存储过程仅支持常用SQL语句。若发现问题,请联系[email protected],共同进步!!版本更新:Version 1.0.1:修复了查询第一页数据时会采用错误的SQL语句的BUGVersion 1.0.2:更新了部分参数的解释Version 1.0.3:修复了多表查询时由于表名错误导致的出错Version 1.0.4:去除了部分不需要的条件判断语句Version 1.0.5:修复了第一页时GROUP BY的错误,现在可以在@FldName里面加入COUNT()、MAX()等(不限于此)聚合函数注意:1.HAVING语句需要与GROUP BY语句配合使用,格式为:GROUP BY XXX HAVING XXX。 2.@FldSort需要给需要排序的字段设置ASC或者DESC。 3.@strOrder为排序&聚合参数,排序时在没有设置@FldSort时起作用(默认正序排列);聚合时为了计算总条数,若该参数为空时则取@FldSort的第一个字段。 4.建议@strOrder设置为主键,就算不是主键也不要包含NULL,否则会发生不可预料的结果。若该参数为空,请务必使@FldSort的第一个字段不含NULL。*/CREATE PROCEDURE [dbo].[MyPageRead](@TblName nvarchar(3000) --连接的表名,即FROM后面的内容,@FldName nvarchar(3000)='*' --要查询的字段名称,默认为全部,@FldSort nvarchar(3000)=NULL --排序字段,不需要ORDER BY,排序自行设置,请加入ASC或者DESC,@strCondition nvarchar(3000)=NULL --要查询的语句,不需要WHERE,前面不需要跟AND或者OR,但是不会影响计算,@strGroup nvarchar(3000)=NULL --要聚合的语句,不需要GROUP BY,@strHaving nvarchar(3000)=NULL --HAVING语句,不需要HAVING,@Dist bit=0 --是否去除重复数据,0不去除/1去除,@strOrder nvarchar(1000)=NULL --一个排序字段,当@FldSort为空时必须指定。而且该字段用于计算总条数,该字段为空时选取@FldSort的第一个字段,@OnlyCounts bit=0 --是否只返回总条数而不进行分页,@PageSize int=10 --每页要显示的数量,@Page int=1 --要显示那一页的数据,@Counts int=1 output --返回总条数,@PageCounts int=1 output --返回总页数)ASSET NOCOUNT ON --不返回计数--定义变量DECLARE @tmpFldSort nvarchar(3000) --构成的ORDER BY语句存放处DECLARE @tmpstrCondition nvarchar(3000) --WHERE语句存放处DECLARE @tmpstrGroup nvarchar(3000) --GROUP BY语句存放处DECLARE @tmpstrfirst nvarchar(3000) --1.开头存放处,控制DistDECLARE @tmpstrfirstCount nvarchar(3000) --2.开头存放处,控制Dist/*计算时间*/DECLARE @StartTime datetimeSET @StartTime=GETDATE()IF (@FldSort IS NULL OR @FldSort='') AND (@strOrder IS NULL OR @strOrder='') RETURN--必须有一个有值,如果有问题,直接跳出IF @FldSort IS NULL OR @FldSort='' SET @tmpFldSort=@strOrder+' ASC 'ELSE SET @tmpFldSort=@FldSort--以上为设置ORDER BY语句IF @strCondition IS NULL OR @strCondition='' SET @tmpstrCondition=''ELSE BEGIN IF CHARINDEX('AND ',LTRIM(@strCondition))=1 SET @strCondition=RIGHT(@strCondition,LEN(@strCondition)-4) IF CHARINDEX('OR ',LTRIM(@strCondition))=1 SET @strCondition=RIGHT(@strCondition,LEN(@strCondition)-3) SET @tmpstrCondition=' WHERE '+@strCondition END--以上为设置WHERE语句IF @strGroup IS NULL OR @strGroup='' SET @tmpstrGroup=''ELSE BEGIN SET @tmpstrGroup=' GROUP BY '+@strGroup IF @strHaving IS NOT NULL AND @strHaving'' SET @tmpstrGroup=@tmpstrGroup+' HAVING '+@strHaving END--以上为设置GROUP BY语句DECLARE @tmpFldsubstr nvarchar(1000) --排序的第一个字段,用于计算总数据量,当@strOrder无数据时有效IF @Dist=0 BEGIN SET @tmpstrfirst=' SELECT ' IF @strOrder IS NULL OR @strOrder='' BEGIN SET @tmpFldsubstr=LEFT(LTRIM(@FldSort),CHARINDEX(CHAR(32),LTRIM(@FldSort))) SET @tmpstrfirstCount=' SELECT @Counts=COUNT('+@tmpFldsubstr+')' END ELSE SET @tmpstrfirstCount=' SELECT @Counts=COUNT('+@strOrder+')' ENDELSE BEGIN SET @tmpstrfirst=' SELECT DISTINCT ' IF @strOrder IS NULL OR @strOrder='' BEGIN SET @tmpFldsubstr=LEFT(LTRIM(@FldSort),CHARINDEX(CHAR(32),LTRIM(@FldSort))) SET @tmpstrfirstCount=' SELECT @Counts=COUNT(DISTINCT '+@tmpFldsubstr+')' END ELSE SET @tmpstrfirstCount=' SELECT @Counts=COUNT(DISTINCT '+@strOrder+')' END--以上通过@Dist设置@CountsDECLARE @sqlStr nvarchar(3000) --查询的sql语句IF @tmpstrGroup='' SET @sqlStr=@tmpstrfirstCount+' FROM '+@TblName+@tmpstrConditionELSE BEGIN SET @tmpstrfirstCount=REPLACE(@tmpstrfirstCount,'@Counts=','') SET @sqlStr='SELECT @Counts=COUNT(*) FROM ('+@tmpstrfirstCount+'AS tmpF FROM '+@TblName+@tmpstrCondition+@tmpstrGroup+') AS tmpT' ENDEXEC sp_executesql @sqlStr,N'@Counts int out ',@Counts out --返回查找到的总数IF @OnlyCounts=1 RETURN--如果@OnlyCounts=1,则直接返回总条数DECLARE @tmpCounts int IF @Counts=0 SET @tmpCounts=1 ELSE SET @tmpCounts=@CountsSET @PageCounts=(@tmpCounts+@PageSize-1)/@<strong>本文来源gao@daima#com搞(%代@#码@网2</strong>PageSize--以上获得分页总数IF @Page@PageCounts SET @Page=@PageCounts--以上设置分页DECLARE @tmpsql nvarchar(3000) --设置最后要查询的SQL语句IF @Page=1 --当取第一页时,用最快的算法 SET @tmpsql=@tmpstrfirst+' TOP '+CAST(@PageSize AS nvarchar(50))+' '+@FldName+' FROM '+@TblName+@tmpstrCondition+@tmpstrGroup+' ORDER BY '+@tmpFldSortIF @Page>1 AND @Page1 AND @Page>@PageCounts/2 --从后面查在总分页数的后半数据 BEGIN SET @tmpFldSort=REPLACE(@tmpFldSort,' ASC',' [~1]') SET @tmpFldSort=REPLACE(@tmpFldSort,' DESC',' [~2]') SET @tmpFldSort=REPLACE(@tmpFldSort,'[~1]','DESC') SET @tmpFldSort=REPLACE(@tmpFldSort,'[~2]','ASC') --优化后半部分数据查询,把查询条件互换,[~1]为DESC,[~2]为ASC SET @tmpsql='WITH temptbl AS(SELECT TOP '+CAST(@Counts-(@Page-1)*@PageSize AS nvarchar(50))+' ROW_NUMBER() OVER(ORDER BY '+@tmpFldSort+') AS tmpRowIndex,'+@FldName+' FROM '+@TblName+' '+@tmpstrCondition+' '+@tmpstrGroup+') ' SET @tmpsql=@tmpsql+'SELECT * FROM temptbl WHERE [tmpRowIndex] BETWEEN '+CAST(@Counts-((@Page-1)*@PageSize+@PageSize-1) AS nvarchar(50))+' AND '+CAST(@Counts-((@Page-1)*@PageSize) AS nvarchar(50))+' ORDER BY tmpRowIndex DESC' END--SELECT @tmpsql 查看拼接的字符串EXEC sp_executesql @tmpsql/*计算时间*/--SELECT DATEDIFF(MS,@StartTime,GETDATE()) AS [Time]/**/SET NOCOUNT OFF