SQLServerProcedurePagination_basic：ALTERPROCEDURE[qiancheng].[Pagination_basic] (@Table_nameVARCHAR(255),--name of table@Rows_targetVARCHAR(1000) ='*',--search rows@Rows_conditionVARCHAR(1000) ='',--the condition to find target (no where)@Rows_orderVARCHAR(255) ='',--the rows to rank@Order_typeINT= 0,-- *Q*C* 0 normal 1 down@PageSizesINT= 10,--the size of each page@PageIndexINT= 1,--current page@ShowPagesINT,--whether show the pages *Q*C* 1-yes 0-no@ShowRecordsINT,--whether show the record *Q*C* 1-yes 0-no@Records_totalINTOUTPUT,--returned total records@Pages_totalINTOUTPUT--returned total pages)ASDECLARE@MainSQL_QC nvarchar (2000)--Main SQL sentenceDECLARE@Var_QCVARCHAR(100)--Temporary variateDECLARE@Order_QCVARCHAR(400)--the sort to rankSET@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_total int out',@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 ='<(select div="" class="line number57 index56 alt2" order_qc=" order by [" rows_order="" var_qc="'">(select max'SET@Order_QC =' order by ['+ @Rows_order +'] asc'ENDIF @PageIndex = 1BEGINIF @Rows_condition !=''SET@MainSQL_QC ='select top '+ str(@PageSizes) +' '+@Rows_target +' from ['+ @Table_name +'] where '+ @Rows_condition +' '+ @Order_QCELSESET@MainSQL_QC ='select top '+ str(@PageSizes) +' '+@Rows_target +' from ['+ @Table_name +'] '+ @Order_QCENDELSEBEGINIF @Rows_condition !=''SET@MainSQL_QC ='select top '+ str(@PageSizes) +' '+@Rows_target +' from ['+ @Table_name +'] where ['+ @Rows_order +']'+ @Var_QC +'(['+ @Rows_order +']) from (select top '+ str((@PageIndex - 1) *@PageSizes) +' ['+ @Rows_order +'] from ['+ @Table_name +'] where '+ @Rows_condition +' '+ @Order_QC +') as Tmep_QC) and '+ @Rows_condition +' '+ @Order_QCELSESET@MainSQL_QC ='select top '+ str(@PageSizes) +' '+@Rows_target +' from ['+ @Table_name +'] where ['+ @Rows_order +']'+ @Var_QC +'(['+ @Rows_order +']) from (select top '+ str((@PageIndex - 1) *@PageSizes) +' ['+ @Rows_order +'] from ['+ @Table_name +']'+ @Order_QC +') as Tmep_QC)'+ @Order_QCENDEXEC(@MainSQL_QC)
调用：execute pagination_basic 'UserDetail','*','','id',Ƈ',Ƌ',Ƈ',Ƈ',Ƈ','',''
主要是末尾的语句，拆分下来便是这样：
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存储过程语法】SQLServer存储过程实现单条件分页
http://m.bbyears.com/jiaocheng/132549.html
推荐访问: