资源下载 - 从此开始!

下载我_精品源码软件教程资源网

您的位置:首页 > 建站教程 > 数据库教程 > MSSQL教程 > 正文

SQLServer存储过程实现单条件分页

2017-12-09 11:32 来源:未知 编辑:admin

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 倒序输出若列 小于之前页数的最小值


打赏一下,我们会为大家提供更多优质资源!

相关文章,95%的人会看!{有内幕、有真相...}

看过本文的人还看过

关闭

微信扫一扫

站长微信账号