澳门新葡亰亚洲在线通用分页存储过程,源码共享,大家共同完善

/*–用存储过程实现的分页程序 显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询,其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法 –邹建2003.09–*/ /*–调用示例
execp_show’地区资料’
execp_show’地区资料’,5,3,’地区编号,地区名称,助记码’,’地区编号’ –*/
/*
因为要顾及通用性,所以对带排序的查询语句有一定要求.如果先排序,再出结果.就是:
execp_show’selecttop100percent*from地区资料orderby地区名称’,5,3,’地区编号,地区名称,助记码’,’地区名称’
–查询语句加上:top100percent//top时 */
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_show]’)andOBJECTPROPERTY(id,N’IsProcedure’)=1)
dropprocedure[dbo].[p_show] GO CreateProcp_show
@QueryStrnvarchar(4000),–表名、视图名、查询语句
@PageSizeint=10,–每页的大小(行数) @PageCurrentint=1,–要显示的页
@FdShownvarchar(4000)=”,–要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrdernvarchar(1000)=”–排序字段列表 as
declare@FdNamenvarchar(250)–表中的主键或表、临时表中的标识列名
,@Id1varchar(20),@Id2varchar(20)–开始和结束的记录号
,@Obj_IDint–对象ID –表中有复合主键的处理
declare@strfdnvarchar(2000)–复合主键列表
,@strjoinnvarchar(4000)–连接字段 ,@strwherenvarchar(2000)–查询条件
select@Obj_ID=object_id(@QueryStr)
,@FdShow=caseisnull(@FdShow,”)when”then’*’else”+@FdShowend
,@FdOrder=caseisnull(@FdOrder,”)when”then”else’orderby’+@FdOrderend
,@QueryStr=casewhen@Obj_IDisnotnullthen”+@QueryStrelse'(‘+@QueryStr+’)a’end
–如果显示第一页,可以直接用top来完成 if@PageCurrent=1 begin
select@Id1=cast(@PageSizeasvarchar(20))
exec(‘selecttop’+@Id1+@FdShow+’from’+@QueryStr+@FdOrder) return end
–如果是表,则检查表中是否有标识更或主键
if@Obj_IDisnotnullandobjectproperty(@Obj_ID,’IsTable’)=1 begin
select@Id1=cast(@PageSizeasvarchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSizeasvarchar(20))
select@FdName=namefromsyscolumnswhereid=@Obj_IDandstatus=0x80
if@@rowcount=0–如果表中无标识列,则检查表中是否有主键 begin
ifnotexists(select1fromsysobjectswhereparent_obj=@Obj_IDandxtype=’PK’)
gotolbusetemp–如果表中无主键,则用临时表处理
select@FdName=namefromsyscolumnswhereid=@Obj_IDandcolidin(
selectcolidfromsysindexkeyswhere@Obj_ID=idandindidin(
selectindidfromsysindexeswhere@Obj_ID=idandnamein(
selectnamefromsysobjectswherextype=’PK’andparent_obj=@Obj_ID )))
if@@rowcount1–检查表中的主键是否为复合主键 begin
select@strfd=”,@strjoin=”,@strwhere=”
select@strfd=@strfd+’,[‘+name+’]’
,@strjoin=@strjoin+’anda.[‘+name+’]=b.[‘+name+’]’
,@strwhere=@strwhere+’andb.[‘+name+’]isnull’
fromsyscolumnswhereid=@Obj_IDandcolidin(
selectcolidfromsysindexkeyswhere@Obj_ID=idandindidin(
selectindidfromsysindexeswhere@Obj_ID=idandnamein(
selectnamefromsysobjectswherextype=’PK’andparent_obj=@Obj_ID )))
select@strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000) gotolbusepk end end end else
gotolbusetemp /*–使用标识列或主键为单一字段的处理方法–*/
lbuseidentity: exec(‘selecttop’+@Id1+@FdShow+’from’+@QueryStr
+’where’+@FdName+’notin(selecttop’
+@Id2+”+@FdName+’from’+@QueryStr+@FdOrder +’)’+@FdOrder ) return
/*–表中有复合主键的处理方法–*/ lbusepk:
exec(‘select’+@FdShow+’from(selecttop’+@Id1+’a.*from
(selecttop100percent*from’+@QueryStr+@FdOrder+’)a
leftjoin(selecttop’+@Id2+”+@strfd+’
from’+@QueryStr+@FdOrder+’)bon’+@strjoin+’ where’+@strwhere+’)a’ )
return /*–用临时表处理的方法–*/ lbusetemp:
select@FdName='[ID_’+cast(newid()asvarchar(40))+’]’
,@Id1=cast(@PageSize*(@PageCurrent-1)asvarchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1asvarchar(20))
exec(‘select’+@FdName+’=identity(int,0,1),’+@FdShow+’
into#tbfrom’+@QueryStr+@FdOrder+’
select’+@FdShow+’from#tbwhere’+@FdName+’between’ +@Id1+’and’+@Id2 ) GO

好久没有上来写点东西了,今天正好有空,共享一些个人心得,就是关于分页的存储过程,这个问题应该是老生重谈了,网上的通用存储过程的类型已经够多了,但是,好象看到的基本上不能够满足一些复杂的SQL语句的分页,比如下面这句:
select”asCheckBox,A.TargetID,A.TargetPeriod,Convert(varchar(10),B.BeginDate,120)asBeginDate,
Convert(varchar(10),B.EndDate,120)asEndDate,C.SalesCode,C.SalesName,D.CatalogCode,D.CatalogName,
E.OrgID,E.OrgName,F.OrgIDasBranchOrgID,F.OrgCodeasBranchOrgCode,F.OrgNameasBranchOrgName,
A.Amount,”asDetailButton fromChlSalesTargetasA
leftouterjoinChlSalesTargetPeriodasBonA.TargetPeriod=B.TargetPeriod
leftouterjoinChlSalesasConA.Sales=C.SalesCode
leftouterjoinChlItemCatalogasDonA.ItemCatalog=D.CatalogCode
leftouterjoinChlOrgasEonA.OrgID=E.OrgID
leftouterjoinChlOrgasFonC.BranchOrgID=F.OrgID
whereA.TargetPeriod=’200607’andA.TargetPeriod=’200608’andF.OrgCodelike’%123%’andE.OrgCodelike’%123%’
orderbyA.TargetPerioddesc,C.SalesName,D.CatalogName上面这句SQL里面有一些特殊情况,比如使用了Convert函数,而且没有主键,有多表连接,有表别名,字段别名等等,这些情况处理起来可能比较棘手,当然,其中的“”asCheckBox”是我系统当中的特例情况,用来做一些处理的。
我这里提供一个自己开发的通用分页存储过程,有什么好的建议和意见,大家请不吝指教。代码如下:

通用分页存储过程—-Sp_Paging /**//*

功能:通用分页存储过程 参数:
@PKvarchar(50),主键,用来排序的单一字段,空的话,表示没有主键,存储过程将自动创建标识列主键
@Fieldsvarchar(500),要显示的字段列表(格式如:ID,Code,Name)
@Tablesvarchar(1000),要使用的表集合(Org)
@Wherevarchar(500),查询条件(Codelike’100′)
@OrderByvarchar(100),排序条件(支持多个排序字段,如:ID,Codedesc,Namedesc)
@PageIndexint,当前要显示的页的页索引,索引从1开始,无记录时为0。
@PageSizeint,页大小 创建者:HollisYao 创建日期:2006-08-06 备注:
============================================================ */
CreatePROCEDURE[dbo].[Sp_Paging] @PKvarchar(50)=”,
@Fieldsvarchar(500), @Tablesvarchar(1000), @Wherevarchar(500)=”,
@OrderByvarchar(100), @PageIndexint, @PageSizeint AS
–替换单引号,避免构造SQL出错 set@Fields=replace(@Fields,””,”””)
–要执行的SQL,切分为几个字符串,避免出现长度超过4k时的问题
declare@SQL1varchar(4000) declare@SQL2varchar(4000) set@SQL1=”
set@SQL2=” if@Whereisnotnullandlen(ltrim(rtrim(@Where)))0
set@Where=’where’+@Where else set@Where=’where1=1′
set@SQL1=@SQL1+’declare@TotalCountint’–声明一个变量,总记录数
set@SQL1=@SQL1+’declare@PageCountint’–声明一个变量,总页数
set@SQL1=@SQL1+’declare@PageIndexint’–声明一个变量,页索引
set@SQL1=@SQL1+’declare@StartRowint’–声明一个变量,当前页第一条记录的索引
set@SQL1=@SQL1+’select@TotalCount=count(*)from’+@Tables+@Where–获取总记录数
set@SQL1=@SQL1+’if@PageCount=0begin’–如果记录数为0,直接输出空的结果集
set@SQL1=@SQL1+’select’+@Fields+’from’+@Tables+’where11′
set@SQL1=@SQL1+’select0asPageIndex,0asPageCount,’+convert(varchar,@PageSize)+’asPageSize,0asTotalCount’
set@SQL1=@SQL1+’returnend’
set@SQL1=@SQL1+’set@PageCount=(@TotalCount+’+convert(varchar,@PageSize)+’-1)/’+convert(varchar,@PageSize)–获取总页数
set@SQL1=@SQL1+’set@PageIndex=’+convert(varchar,@PageIndex)–设置正确的页索引
set@SQL1=@SQL1+’if@PageIndex0set@PageIndex=1′
set@SQL1=@SQL1+’if@PageIndex@PageCountand@PageCount0set@PageIndex=@PageCount’
set@SQL1=@SQL1+’set@StartRow=(@PageIndex-1)*’+convert(varchar,@PageSize)+’+1′
if(charindex(‘,’,@OrderBy)=0andcharindex(@PK,@OrderBy)0) begin
–****************************************************************************
–****************不需要创建主键********************************************
–****************************************************************************
declare@SortDirectionvarchar(10)–排序方向,=:升序,=:倒序
set@SortDirection=’=’ ifcharindex(‘desc’,@OrderBy)0
set@SortDirection=’=’
set@SQL2=@SQL2+’declare@Sortvarchar(100)’–声明一个变量,用来记录当前页第一条记录的排序字段值
set@SQL2=@SQL2+’setrowcount@StartRow’–设置返回记录数截止到当前页的第一条
set@SQL2=@SQL2+’select@Sort=’+@PK+’from’+@Tables+@Where+’orderby’+@OrderBy–获取当前页第一个排序字段值
set@SQL2=@SQL2+’setrowcount’+convert(varchar,@PageSize)–设置返回记录数为页大小
set@Where=@Where+’and’+@PK+@SortDirection+’@Sort’
set@SQL2=@SQL2+’select’+@Fields+’from’+@Tables+@Where+’orderby’+@OrderBy–输出最终显示结果
end else begin
–****************************************************************************
–*************需要创建自增长主键******************************************
–****************************************************************************
set@SQL2=@SQL2+’declare@EndRowint’
set@SQL2=@SQL2+’set@EndRow=@PageIndex*’+convert(varchar,@PageSize)
set@SQL2=@SQL2+’setrowcount@EndRow’
set@SQL2=@SQL2+’declare@PKBeginint’–声明一个变量,开始索引
set@SQL2=@SQL2+’declare@PKEndint’–声明一个变量,结束索引
set@SQL2=@SQL2+’set@PKBegin=@StartRow’
set@SQL2=@SQL2+’set@PKEnd=@EndRow’
–****************************************************************************
–************澳门新葡亰亚洲在线,对特殊字段进行转换,以便可以插入到临时表******************
–****************************************************************************
declare@TempFieldsvarchar(500) set@TempFields=@Fields
set@TempFields=replace(@TempFields,””’asCheckBox’,”)
set@TempFields=replace(@TempFields,””’asDetailButton’,”)
set@TempFields=replace(@TempFields,””’asRadio’,”)
set@TempFields=LTRIM(RTRIM(@TempFields))
ifleft(@TempFields,1)=’,’–去除最左边的逗号
set@TempFields=substring(@TempFields,2,len(@TempFields))
ifright(@TempFields,1)=’,’–去除最右边的逗号
set@TempFields=substring(@TempFields,1,len(@TempFields)-1)
set@SQL2=@SQL2+’selectidentity(int,1,1)asPK,’+@TempFields+’into#tbfrom’+@Tables+@Where+’orderby’+@OrderBy
–****************************************************************************
–********去除字段的表名前缀,当有字段有别名时,只保留字段别名*********
–****************************************************************************
declare@TotalFieldsvarchar(500) declare@tmpvarchar(50) declare@iint
declare@jint declare@iLeftint–左括号的个数
declare@iRightint–右括号的个数 set@i=0 set@j=0 set@iLeft=0 set@iRight=0
set@tmp=” set@TotalFields=” while(len(@Fields)0) begin
set@i=charindex(‘,’,@Fields) –去除字段的表名前缀 if(@i=0) begin
–找不到逗号分割,即表示只剩下最后一个字段 set@tmp=@Fields end else
begin set@tmp=substring(@Fields,1,@i) end set@j=charindex(‘.’,@tmp)
if(@j0) set@tmp=substring(@tmp,@j+1,len(@tmp))
–*******当有字段有别名时,只保留字段别名*********
–带括号的情况要单独处理,如Convert(varchar(10),B.EndDate,120)asEndDate
while(charindex(‘(‘,@tmp)0) begin set@iLeft=@iLeft+1
set@tmp=substring(@tmp,charindex(‘(‘,@tmp)+1,Len(@tmp)) end
while(charindex(‘)’,@tmp)0) begin set@iRight=@iRight+1
set@tmp=substring(@tmp,charindex(‘)’,@tmp)+1,Len(@tmp)) end
–当括号恰好组队的时候,才能进行字段别名的处理 if(@iLeft=@iRight) begin
set@iLeft=0 set@iRight=0
–不对这几个特殊字段作处理:CheckBox、DetailButton、Radio
if(charindex(‘CheckBox’,@tmp)=0andcharindex(‘DetailButton’,@tmp)=0andcharindex(‘Radio’,@tmp)=0)
begin –判断是否有别名
if(charindex(‘as’,@tmp)0)–别名的第一种写法,带’as’的格式 begin
set@tmp=substring(@tmp,charindex(‘as’,@tmp)+2,len(@tmp)) end else begin
if(charindex(”,@tmp)0)–别名的第二种写法,带空格(“”)的格式 begin
while(charindex(”,@tmp)0) begin
set@tmp=substring(@tmp,charindex(”,@tmp)+1,len(@tmp)) end end end end
set@TotalFields=@TotalFields+@tmp end if(@i=0) set@Fields=” else
set@Fields=substring(@Fields,@i+1,len(@Fields)) end –print@TotalFields
set@SQL2=@SQL2+’select’+@TotalFields+’from#tbwherePKbetween@PKBeginand@PKEndorderbyPK’–输出最终显示结果
set@SQL2=@SQL2+’droptable#tb’ end
–输出“PageIndex(页索引)、PageCount(页数)、PageSize(页大小)、TotalCount(总记录数)”
set@SQL2=@SQL2+’select@PageIndexasPageIndex,@PageCountasPageCount,’
+convert(varchar,@PageSize)+’asPageSize,@TotalCountasTotalCount’
–print@SQL1+@SQL2 exec(@SQL1+@SQL2)
如果使用这个通用分页存储过程的话,那么调用方法如下:

使用通用分页存储过程进行分页 /**//*

功能:获取销售目标,根据条件 参数: @UserTypeint, @OrgIDvarchar(500),
@TargetPeriodBeginnvarchar(50), @TargetPeriodEndnvarchar(50),
@BranchOrgCodenvarchar(50), @BranchOrgNamenvarchar(50),
@OrgCodenvarchar(50), @OrgNamenvarchar(50), @SalesCodenvarchar(50),
@SalesNamenvarchar(50), @CatalogCodenvarchar(50),
@CatalogNamenvarchar(50),
@PageIndexint,当前要显示的页的页索引,索引从1开始,无记录时为0。
@PageSizeint,页大小 创建者:HollisYao 创建日期:2006-08-11 备注:
============================================================ */
CreatePROCEDURE[dbo].[GetSalesTargetList] @UserTypeint,
@OrgIDnvarchar(500), @TargetPeriodBeginnvarchar(50),
@TargetPeriodEndnvarchar(50), @BranchOrgCodenvarchar(50),
@BranchOrgNamenvarchar(50), @OrgCodenvarchar(50), @OrgNamenvarchar(50),
@SalesCodenvarchar(50), @SalesNamenvarchar(50),
@CatalogCodenvarchar(50), @CatalogNamenvarchar(50), @PageIndexint,
@PageSizeint AS declare@Conditionnvarchar(2000) set@Condition=”
if(@UserType1) set@Condition=@Condition+’andA.OrgIDin(‘+@OrgID+’)’
if(len(@TargetPeriodBegin)0)
set@Condition=@Condition+’andA.TargetPeriod=”’+@TargetPeriodBegin+””
if(len(@TargetPeriodEnd)0)
set@Condition=@Condition+’andA.TargetPeriod=”’+@TargetPeriodEnd+””
if(len(@BranchOrgCode)0)
set@Condition=@Condition+’andF.OrgCodelike”%’+@BranchOrgCode+’%”’
if(len(@BranchOrgName)0)
set@Condition=@Condition+’andF.OrgNamelike”%’+@BranchOrgName+’%”’
if(len(@OrgCode)0)
set@Condition=@Condition+’andE.OrgCodelike”%’+@OrgCode+’%”’
if(len(@OrgName)0)
set@Condition=@Condition+’andE.OrgNamelike”%’+@OrgName+’%”’
if(len(@SalesCode)0)
set@Condition=@Condition+’andC.SalesCodelike”%’+@SalesCode+’%”’
if(len(@SalesName)0)
set@Condition=@Condition+’andC.SalesNamelike”%’+@SalesName+’%”’
if(len(@CatalogCode)0)
set@Condition=@Condition+’andD.CatalogCodelike”%’+@CatalogCode+’%”’
if(len(@CatalogName)0)
set@Condition=@Condition+’andD.CatalogNamelike”%’+@CatalogName+’%”’
if(len(@Condition)0)
set@Condition=substring(@Condition,5,len(@Condition)) –print@Condition
execsp_Paging
N”,N”’asCheckBox,A.TargetID,A.TargetPeriod,Convert(varchar(10),B.BeginDate,120)asBeginDate,Convert(varchar(10),B.EndDate,120)asEndDate,
C.SalesCode,C.SalesName,D.CatalogCode,D.CatalogName,E.OrgID,E.OrgName,F.OrgIDasBranchOrgID,F.OrgCodeasBranchOrgCode,F.OrgNameasBranchOrgName,A.Amount,”asDetailButton’,
N’ChlSalesTargetasA
leftouterjoinChlSalesTargetPeriodasBonA.TargetPeriod=B.TargetPeriod
leftouterjoinChlSalesasConA.Sales=C.SalesCode
leftouterjoinChlItemCatalogasDonA.ItemCatalog=D.CatalogCode
leftouterjoinChlOrgasEonA.OrgID=E.OrgID
leftouterjoinChlOrgasFonC.BranchOrgID=F.OrgID’, @Condition,
N’A.TargetPerioddesc,C.SalesName,D.CatalogName’, @PageIndex,@PageSize

发表评论

电子邮件地址不会被公开。 必填项已用*标注