前段时间我收集对比了许多 MSSQL 上的分页存储过程，然后写了一个个人认为很高效，功能也较多的分页存储过程——MyPageRead。
以后就将这个文章页作为该分页存储过程的发布页，有更新了就第一时间更新到这里。
Version 1.0.5 2014.07.15更新
本存储过程是本人查找对比网络上常用的分页存储过程后改写的分页存储过程，拥有较高效率，但不保证效率最高。
本存储过程是在 MSSQL 2012 上编写，不保证兼容所有版本 MSSQL（已知 MSSQL 2005 需要修改少量代码）。不兼容其他数据库。
本分页存储过程仅支持常用 SQL 语句。
若发现问题，请联系 ttio4116@live.com 或在此博客留言，共同进步！！
一些注意事项见文件内的 README
注意：在执行多表联合查询时，若几张表包含重复的字段，如 table1.col、table2.col，存储过程会出错，这时请不要使用”*”号，将所有要用的字段全列出来。这也是保持良好 SQL 语句书写规范。
也可以在我的开源中国发布的页面 请点这里 查看下载
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
README
Version 1.0.5
本存储过程是本人查找对比网络上常用的分页存储过程后改写的分页存储过程，拥有较高效率，但不保证效率最高。
本存储过程是在MSSQL2012上编写，不保证兼容所有版本MSSQL（已知MSSQL2005需要修改少量代码）。不兼容其他数据库。
本分页存储过程仅支持常用SQL语句。
若发现问题，请联系ttio4116@live.com，共同进步！！
版本更新：
Version 1.0.1：修复了查询第一页数据时会采用错误的SQL语句的BUG
Version 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 --返回总页数
)
AS
SET NOCOUNT ON --不返回计数
--定义变量
DECLARE @tmpFldSort nvarchar(3000) --构成的ORDER BY语句存放处
DECLARE @tmpstrCondition nvarchar(3000) --WHERE语句存放处
DECLARE @tmpstrGroup nvarchar(3000) --GROUP BY语句存放处
DECLARE @tmpstrfirst nvarchar(3000) --1.开头存放处，控制Dist
DECLARE @tmpstrfirstCount nvarchar(3000) --2.开头存放处，控制Dist
/*计算时间*/
DECLARE @StartTime datetime
SET @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+')'
END
ELSE
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设置@Counts
DECLARE @sqlStr nvarchar(3000) --查询的sql语句
IF @tmpstrGroup=''
SET @sqlStr=@tmpstrfirstCount+' FROM '+@TblName+@tmpstrCondition
ELSE
BEGIN
SET @tmpstrfirstCount=REPLACE(@tmpstrfirstCount,'@Counts=','')
SET @sqlStr='SELECT @Counts=COUNT(*) FROM ('+@tmpstrfirstCount+'AS tmpF FROM '+@TblName+@tmpstrCondition+@tmpstrGroup+') AS tmpT'
END
EXEC 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=@Counts
SET @PageCounts=(@tmpCounts+@PageSize-1)/@PageSize
--以上获得分页总数
IF @Page<1 SET @Page=1 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 '+@tmpFldSort
IF @Page>1 AND @Page<=@PageCounts/2 --这是要查询的页在总分页数的前半 BEGIN SET @tmpsql='WITH temptbl AS(SELECT TOP '+CAST(@Page*@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((@Page-1)*@PageSize+1 AS nvarchar(50))+' AND '+CAST((@Page-1)*@PageSize+@PageSize AS nvarchar(50)) END IF @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 '+@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
下载地址：百度网盘
原创文章，转载请以链接形式注明出处：https://blog.ttionya.com/article-119.html