MSSQL分页存储过程

概述MSSQL分页存储过程

下面是内存溢出 jb51.cc 通过网络收集整理的代码片段。

内存溢出小编现在分享给大家，也给大家做个参考。

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。      [email protected]需要给需要排序的字段设置ASC或者DESC。      [email protected]为排序&聚合参数，排序时在没有设置@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 @[email protected]+' ASC 'ELSE	SET @[email protected]--以上为设置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 ',LEN(@strCondition)-3)		SET @tmpstrCondition=' WHERE '[email protected]	END--以上为设置WHERE语句IF @strGroup IS NulL OR @strGroup=''	SET @tmpstrGroup=''ELSE	BEGIN		SET @tmpstrGroup=' GROUP BY '[email protected]		IF @strHaving IS NOT NulL AND @strHaving<>''			SET @[email protected]+' HAVING '[email protected]	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('[email protected]+')'			END		ELSE			SET @tmpstrfirstCount=' SELECT @Counts=COUNT('[email protected]+')'	ENDELSE	BEGIN		SET @tmpstrfirst=' SELECT disTINCT '		IF @strOrder IS NulL OR @strOrder=''			BEGIN				SET @tmpFldsubstr=left(LTRIM(@FldSort),LTRIM(@FldSort)))				SET @tmpstrfirstCount=' SELECT @Counts=COUNT(disTINCT '[email protected]+')'			END		ELSE			SET @tmpstrfirstCount=' SELECT @Counts=COUNT(disTINCT '[email protected]+')'	END--以上通过@dist设置@CountsDECLARE @sqlStr nvarchar(3000)     --查询的SQL语句IF @tmpstrGroup=''	SET @[email protected]+' FROM '[email protected][email protected]ELSE	BEGIN		SET @tmpstrfirstCount=REPLACE(@tmpstrfirstCount,'@Counts=','')		SET @sqlStr='SELECT @Counts=COUNT(*) FROM ('[email protected]+'AS tmpF FROM '[email protected][email protected][email protected]+') 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 @[email protected]SET @PageCounts=(@[email protected])/@PageSize--以上获得分页总数IF @Page<1	SET @Page=1IF @Page>@PageCounts	SET @[email protected]--以上设置分页DECLARE @tmpsql nvarchar(3000)     --设置最后要查询的SQL语句IF @Page=1     --当取第一页时，用最快的算法	SET @[email protected]+' top '+CAST(@PageSize AS nvarchar(50))+' '[email protected]+' FROM '[email protected][email protected][email protected]+' ORDER BY '[email protected]IF @Page>1 AND @Page<[email protected]/2     --这是要查询的页在总分页数的前半	BEGIN		SET @tmpsql='WITH temptbl AS(SELECT top '+CAST(@Page*@PageSize AS nvarchar(50))+' ROW_NUMBER() OVER(ORDER BY '[email protected]+') AS tmpRowIndex,'[email protected]+' FROM '[email protected]+' '[email protected]+' '[email protected]+') '		SET @[email protected]+'SELECT * FROM temptbl WHERE [tmpRowIndex] BETWEEN '+CAST((@Page-1)*@PageSize+1 AS nvarchar(50))+' AND '+CAST((@Page-1)*@[email protected] AS nvarchar(50))	ENDIF @Page>1 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 '[email protected]+') AS tmpRowIndex,'[email protected]+' FROM '[email protected]+' '[email protected]+' '[email protected]+') '		SET @[email protected]+'SELECT * FROM temptbl WHERE [tmpRowIndex] BETWEEN '+CAST(@Counts-((@Page-1)*@[email protected]) 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

以上是内存溢出(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

总结

以上是内存溢出为你收集整理的MSSQL分页存储过程全部内容，希望文章能够帮你解决MSSQL分页存储过程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。