MSSQL分页存储过程完整示例(支持多表分页存储)
本文实例讲述了MSSQL分页存储过程。分享给大家供大家参考,具体如下:
USE[DB_Common] GO /******对象:StoredProcedure[dbo].[Com_Pagination]脚本日期:03/09/201223:46:20******/ SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO /************************************************************ * *Sql分页存储过程(支持多表分页存储) * *调用实例: EXECCom_Pagination100,--总记录数 0,--总页数 --'Person',--查询的表名 ' Personp LEFTJOINTEa ONa.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) AS DECLARE@PageCountINT,--总页数 @strSqlNVARCHAR(4000),--主查询语句 @strTempNVARCHAR(2000),--临时变量 @strCountNVARCHAR(1000),--统计语句 @strOrderTypeNVARCHAR(1000)--排序语句 BEGIN SET@PageCount=@PageSize*(@CurrentPage-1) SET@strOrderType='ORDERBY'+@OrderColumn+'' IF@Condition!='' BEGIN IF@CurrentPage=1 BEGIN IF@GROUP=1 BEGIN SET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table +'WHERE'+@Condition+'GROUPBY'+@GroupColumn SET@strCount=@strCount+'SET@TotalCount=@@ROWCOUNT' SET@strSql='SELECTTOP'+STR(@PageSize)+''+@Column +'FROM'+@Table+'WHERE'+@Condition+ 'GROUPBY'+@GroupColumn+''+@strOrderType END ELSE BEGIN SET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table +'WHERE'+@Condition SET@strSql='SELECTTOP'+STR(@PageSize)+''+@Column +'FROM'+@Table+'WHERE'+@Condition+''+@strOrderType END END ELSE BEGIN IF@GROUP=1 BEGIN SET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table +'WHERE'+@Condition+'GROUPBY'+@GroupColumn SET@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) END ELSE BEGIN SET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table +'WHERE'+@Condition SET@strSql='SELECT*FROM(SELECTTOP(2000)'+@Column +',ROW_NUMBER()OVER('+@strOrderType+ ')ASNUMFROM'+@Table+'WHERE'+@Condition+ ')ASTWHERENUMBETWEEN'+STR(@PageCount+1)+ 'AND'+STR(@PageCount+@PageSize) END END END ELSE --没有查询条件 BEGIN IF@CurrentPage=1 BEGIN IF@GROUP=1 BEGIN SET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table +'GROUPBY'+@GroupColumn SET@strCount=@strCount+'SET@TotalCount=@@ROWCOUNT' SET@strSql='SELECTTOP'+STR(@PageSize)+''+@Column +'FROM'+@Table+'GROUPBY'+@GroupColumn+''+ @strOrderType END ELSE BEGIN SET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table SET@strSql='SELECTTOP'+STR(@PageSize)+''+@Column +'FROM'+@Table+''+@strOrderType END END ELSE BEGIN IF@GROUP=1 BEGIN SET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table +'GROUPBY'+@GroupColumn SET@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) END ELSE BEGIN SET@strCount='SELECT@TotalCount=COUNT(*)FROM'+@Table SET@strSql='SELECT*FROM(SELECTTOP(2000)'+@Column +',ROW_NUMBER()OVER('+@strOrderType+ ')ASNUMFROM'+@Table+')ASTWHERENUMBETWEEN'+ STR(@PageCount+1)+'AND'+STR(@PageCount+@PageSize) END END END EXECsp_executesql@strCount, N'@TotalCountINTOUTPUT', @TotalCountOUTPUT IF@TotalCount>2000 BEGIN SET@TotalCount=2000 END IF@TotalCount%@PageSize=0 BEGIN SET@TotalPage=@TotalCount/@PageSize END ELSE BEGIN SET@TotalPage=@TotalCount/@PageSize+1 END SETNOCOUNTON EXEC(@strSql) END SETNOCOUNTOFF /**调用实例: EXECCom_Pagination100,--总记录数 0,--总页数 --'Person',--查询的表名 ' Personp LEFTJOINTEa ONa.PID=p.Id ',--查询的表名(这里为多表) 'a.*',--查询数据列 'p.ID',--排列字段 'p.ID',--分组字段 2,--每页记录数 1,--当前页数 0,--是否使用分组,否是 'a.pid=2'--查询条件 SELECTa.* FROMPersonp LEFTJOINTEa ONa.PID=p.Id WHEREa.pid=2 **/
希望本文所述对大家SQLServer数据库程序设计有所帮助。