最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
SQLServer存储过程实现单条件分页
时间：2022-06-29 07:47:03 编辑：袖梨 来源：一聚教程网
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
)AS
DECLARE@MainSQL_QC nvarchar (2000)--Main SQL sentence
DECLARE@Var_QCVARCHAR(100)--Temporary variate
DECLARE@Order_QCVARCHAR(400)--the sort to rank
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_total int out',@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 ='<(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'
END
IF @PageIndex = 1
BEGIN
IF @Rows_condition !=''
SET@MainSQL_QC ='select top '+ str(@PageSizes) +' '+@Rows_target +' from ['+ @Table_name +'] where '+ @Rows_condition +' '+ @Order_QC
ELSE
SET@MainSQL_QC ='select top '+ str(@PageSizes) +' '+@Rows_target +' from ['+ @Table_name +'] '+ @Order_QC
END
ELSE
BEGIN
IF @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_QC
ELSE
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 +']'+ @Order_QC +') as Tmep_QC)'+ @Order_QC
ENDEXEC(@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 倒序输出若列 小于之前页数的最小值
相关文章
- 《无限暖暖》天星之羽获得位置介绍 12-20
- 《流放之路2》重铸台解锁方法介绍 12-20
- 《无限暖暖》瞄准那个亮亮的成就怎么做 12-20
- 《无限暖暖》魔气怪终结者完成方法 12-20
- 《无限暖暖》曙光毛团获得位置介绍 12-20
- 《无限暖暖》日光果获得位置介绍 12-20