MSSQL中怎么实现分页存储过程，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

具体如下：

USE[DB_Common]GO/******对象:StoredProcedure[dbo].[Com_Pagination]脚本日期:03/09/201223:46:20******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO/**************************************************************Sql分页存储过程（支持多表分页存储）**调用实例：EXECCom_Pagination100,--总记录数0,--总页数--'Person',--查询的表名'PersonpLEFTJOINTEaONa.PID=p.Id',--查询的表名（这里为多表）'a.*',--查询数据列'p.ID',--排列字段'p.ID',--分组字段2,--每页记录数1,--当前页数0,--是否使用分组，否是'a.pid=2'--查询条件************************************************************/CREATEPROCEDURE[dbo].[Com_Pagination]@TotalCountINTOUTPUT,--总记录数@TotalPageINTOUTPUT,--总页数@TableNVARCHAR(1000),--查询的表名（可多表，例如：PersonpLEFTJOINTEaONa.PID=p.Id）@ColumnNVARCHAR(1000),--查询的字段，可多列或者为*@OrderColumnNVARCHAR(100),--排序字段@GroupColumnNVARCHAR(150),--分组字段@PageSizeINT,--每页记录数@CurrentPageINT,--当前页数@GroupTINYINT,--是否使用分组，否是@ConditionNVARCHAR(4000)--查询条件（注意：若这时候为多表查询，这里也可以跟条件，例如：a.pid=2）ASDECLARE@PageCountINT,--总页数@strSqlNVARCHAR(4000),--主查询语句@strTempNVARCHAR(2000),--临时变量@strCountNVARCHAR(1000),--统计语句@strOrderTypeNVARCHAR(1000)--排序语句BEGINSET@PageCount=@PageSize*(@CurrentPage-1)SET@strOrderType='ORDERBY'+@OrderColumn+''IF@Condition!=''BEGINIF@CurrentPage=1BEGINIF@GROUP=1BEGINSET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table+'WHERE'+@Condition+'GROUPBY'+@GroupColumnSET@strCount=@strCount+'SET@TotalCount=@@ROWCOUNT'SET@strSql='SELECTTOP'+STR(@PageSize)+''+@Column+'FROM'+@Table+'WHERE'+@Condition+'GROUPBY'+@GroupColumn+''+@strOrderTypeENDELSEBEGINSET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table+'WHERE'+@ConditionSET@strSql='SELECTTOP'+STR(@PageSize)+''+@Column+'FROM'+@Table+'WHERE'+@Condition+''+@strOrderTypeENDENDELSEBEGINIF@GROUP=1BEGINSET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table+'WHERE'+@Condition+'GROUPBY'+@GroupColumnSET@strCount=@strCount+'SET@TotalCount=@@ROWCOUNT'SET@strSql='SELECT*FROM(SELECTTOP(2000)'+@Column+',ROW_NUMBER()OVER('+@strOrderType+')ASNUMFROM'+@Table+'WHERE'+@Condition+'GROUPBY'+@GroupColumn+')ASTWHERENUMBETWEEN'+STR(@PageCount+1)+'AND'+STR(@PageCount+@PageSize)ENDELSEBEGINSET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table+'WHERE'+@ConditionSET@strSql='SELECT*FROM(SELECTTOP(2000)'+@Column+',ROW_NUMBER()OVER('+@strOrderType+')ASNUMFROM'+@Table+'WHERE'+@Condition+')ASTWHERENUMBETWEEN'+STR(@PageCount+1)+'AND'+STR(@PageCount+@PageSize)ENDENDENDELSE--没有查询条件BEGINIF@CurrentPage=1BEGINIF@GROUP=1BEGINSET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table+'GROUPBY'+@GroupColumnSET@strCount=@strCount+'SET@TotalCount=@@ROWCOUNT'SET@strSql='SELECTTOP'+STR(@PageSize)+''+@Column+'FROM'+@Table+'GROUPBY'+@GroupColumn+''+@strOrderTypeENDELSEBEGINSET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@TableSET@strSql='SELECTTOP'+STR(@PageSize)+''+@Column+'FROM'+@Table+''+@strOrderTypeENDENDELSEBEGINIF@GROUP=1BEGINSET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table+'GROUPBY'+@GroupColumnSET@strCount=@strCount+'SET@TotalCount=@@ROWCOUNT'SET@strSql='SELECT*FROM(SELECTTOP(2000)'+@Column+',ROW_NUMBER()OVER('+@strOrderType+')ASNUMFROM'+@Table+'GROUPBY'+@GroupColumn+')ASTWHERENUMBETWEEN'+STR(@PageCount+1)+'AND'+STR(@PageCount+@PageSize)ENDELSEBEGINSET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@TableSET@strSql='SELECT*FROM(SELECTTOP(2000)'+@Column+',ROW_NUMBER()OVER('+@strOrderType+')ASNUMFROM'+@Table+')ASTWHERENUMBETWEEN'+STR(@PageCount+1)+'AND'+STR(@PageCount+@PageSize)ENDENDENDEXECsp_executesql@strCount,N'@TotalCountINTOUTPUT',@TotalCountOUTPUTIF@TotalCount>2000BEGINSET@TotalCount=2000ENDIF@TotalCount%@PageSize=0BEGINSET@TotalPage=@TotalCount/@PageSizeENDELSEBEGINSET@TotalPage=@TotalCount/@PageSize+1ENDSETNOCOUNTONEXEC(@strSql)ENDSETNOCOUNTOFF/**调用实例：EXECCom_Pagination100,--总记录数0,--总页数--'Person',--查询的表名'PersonpLEFTJOINTEaONa.PID=p.Id',--查询的表名（这里为多表）'a.*',--查询数据列'p.ID',--排列字段'p.ID',--分组字段2,--每页记录数1,--当前页数0,--是否使用分组，否是'a.pid=2'--查询条件SELECTa.*FROMPersonpLEFTJOINTEaONa.PID=p.IdWHEREa.pid=2**/

看完上述内容，你们掌握MSSQL中怎么实现分页存储过程的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！