SQLServer中怎么利用存储过程实现单条件分页，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

SQLServerProcedurePagination_basic：ALTERPROCEDURE[qiancheng].[Pagination_basic](@Table_nameVARCHAR(255),--nameoftable@Rows_targetVARCHAR(1000)='*',--searchrows@Rows_conditionVARCHAR(1000)='',--theconditiontofindtarget(nowhere)@Rows_orderVARCHAR(255)='',--therowstorank@Order_typeINT=0,--*Q*C*0normal1down@PageSizesINT=10,--thesizeofeachpage@PageIndexINT=1,--currentpage@ShowPagesINT,--whethershowthepages*Q*C*1-yes0-no@ShowRecordsINT,--whethershowtherecord*Q*C*1-yes0-no@Records_totalINTOUTPUT,--returnedtotalrecords@Pages_totalINTOUTPUT--returnedtotalpages)ASDECLARE@MainSQL_QCnvarchar(2000)--MainSQLsentenceDECLARE@Var_QCVARCHAR(100)--TemporaryvariateDECLARE@Order_QCVARCHAR(400)--thesorttorankSET@Records_total=0SET@Pages_total=0IF@ShowRecords=1OR@ShowPages=1BEGINIF@Rows_condition!=''SET@MainSQL_QC='select@Records_total=count(1)from['+@Table_name+']where'+@Rows_conditionELSESET@MainSQL_QC='select@Records_total=count(1)from['+@Table_name+']'EXECsp_executesql@MainSQL_QC,N'@Records_totalintout',@Records_totalOUTPUTENDIF@ShowPages=1BEGINIF@Records_total<=@PageSizesSET@Pages_total=1ELSEBEGINSET@Pages_total=@Records_total/@PageSizesIF(@Records_total%@PageSizes)>0SET@Pages_total=@Pages_total+1ENDENDIF@Order_type=1BEGINSET@Var_QC='<(selectmin'SET@Order_QC='orderby['+@Rows_order+']desc'ENDELSEBEGINSET@Var_QC='>(selectmax'SET@Order_QC='orderby['+@Rows_order+']asc'ENDIF@PageIndex=1BEGINIF@Rows_condition!=''SET@MainSQL_QC='selecttop'+str(@PageSizes)+''+@Rows_target+'from['+@Table_name+']where'+@Rows_condition+''+@Order_QCELSESET@MainSQL_QC='selecttop'+str(@PageSizes)+''+@Rows_target+'from['+@Table_name+']'+@Order_QCENDELSEBEGINIF@Rows_condition!=''SET@MainSQL_QC='selecttop'+str(@PageSizes)+''+@Rows_target+'from['+@Table_name+']where['+@Rows_order+']'+@Var_QC+'(['+@Rows_order+'])from(selecttop'+str((@PageIndex-1)*@PageSizes)+'['+@Rows_order+']from['+@Table_name+']where'+@Rows_condition+''+@Order_QC+')asTmep_QC)and'+@Rows_condition+''+@Order_QCELSESET@MainSQL_QC='selecttop'+str(@PageSizes)+''+@Rows_target+'from['+@Table_name+']where['+@Rows_order+']'+@Var_QC+'(['+@Rows_order+'])from(selecttop'+str((@PageIndex-1)*@PageSizes)+'['+@Rows_order+']from['+@Table_name+']'+@Order_QC+')asTmep_QC)'+@Order_QCENDEXEC(@MainSQL_QC)

调用：execute pagination_basic 'UserDetail','*','','id','1','5','1','1','1','',''

主要是末尾的语句，拆分下来便是这样：

select top 每页数 列名 from [表名] where [排序字段名] < --1 倒序输出若列 小于之前页数的最小值

(select min ( [排序字段名] )from --2 获得一个指定列名中的最小值并输出

(select top (当前页-1)*每页数 [排序字段名] from [表名] where [条件] [排序类型]) --3 选择之前页数总数据倒序输出

as Tmep_QC)--4 建立一个名为Tmep_QC的临时表--2 获得一个指定列名中的最小值并输出

and [条件] [排序类型]--1 倒序输出若列 小于之前页数的最小值

关于SQLServer中怎么利用存储过程实现单条件分页问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。