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 )AS DECLARE@MainSQL_QCnvarchar(2000)--MainSQLsentence DECLARE@Var_QCVARCHAR(100)--Temporaryvariate DECLARE@Order_QCVARCHAR(400)--thesorttorank SET@Records_total=0 SET@Pages_total=0 IF@ShowRecords=1 OR@ShowPages=1 BEGIN IF@Rows_condition!='' SET@MainSQL_QC='select@Records_total=count(1)from['+@Table_name+']where'+@Rows_condition ELSE SET@MainSQL_QC='select@Records_total=count(1)from['+@Table_name+']'EXECsp_executesql@MainSQL_QC, N'@Records_totalintout',@Records_totalOUTPUT END IF@ShowPages=1 BEGIN IF@Records_total<=@PageSizes SET@Pages_total=1 ELSE BEGIN SET@Pages_total=@Records_total/@PageSizes IF(@Records_total%@PageSizes)>0 SET@Pages_total=@Pages_total+1 END END IF@Order_type=1 BEGIN SET@Var_QC='<(selectmin' SET@Order_QC='orderby['+@Rows_order+']desc' END ELSE BEGIN SET@Var_QC='>(selectmax' SET@Order_QC='orderby['+@Rows_order+']asc' END IF@PageIndex=1 BEGIN IF@Rows_condition!='' SET@MainSQL_QC='selecttop'+str(@PageSizes)+''+@Rows_target+'from['+@Table_name+']where'+@Rows_condition+''+@Order_QC ELSE SET@MainSQL_QC='selecttop'+str(@PageSizes)+''+@Rows_target+'from['+@Table_name+']'+@Order_QC END ELSE BEGIN IF@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_QC ELSE 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+']'+@Order_QC+')asTmep_QC)'+@Order_QC ENDEXEC(@MainSQL_QC)
调用：executepagination_basic'UserDetail','*','','id','1','5','1','1','1','',''
主要是末尾的语句，拆分下来便是这样：
selecttop每页数列名from[表名]where[排序字段名]< --1倒序输出若列小于之前页数的最小值
(selectmin([排序字段名])from--2获得一个指定列名中的最小值并输出
(selecttop(当前页-1)*每页数[排序字段名]from[表名]where[条件][排序类型])--3选择之前页数总数据倒序输出
asTmep_QC)--4建立一个名为Tmep_QC的临时表--2获得一个指定列名中的最小值并输出
and[条件][排序类型]--1倒序输出若列小于之前页数的最小值
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持毛票票！