SQL Server通用分页存储过程，源码共享，大家共同完善
2022-11-12 09:52:19
内容摘要
这篇文章主要为大家详细介绍了SQL Server通用分页存储过程，源码共享，大家共同完善，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！好久没有
文章正文
这篇文章主要为大家详细介绍了SQL Server通用分页存储过程，源码共享，大家共同完善，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
好久没有上来写点东西了，今天正好有空，共享一些个人心得，就是关于分页的存储过程，这个问题应该是老生重谈了，网上的通用存储过程的类型已经够多了，但是，好象看到的基本上不能够满足一些复杂的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,''asDetailButtonfromChlSalesTargetasAleftouterjoinChlSalesTargetPeriodasBonA.TargetPeriod=B.TargetPeriodleftouterjoinChlSalesasConA.Sales=C.SalesCodeleftouterjoinChlItemCatalogasDonA.ItemCatalog=D.CatalogCodeleftouterjoinChlOrgasEonA.OrgID=E.OrgIDleftouterjoinChlOrgasFonC.BranchOrgID=F.OrgIDwhereA.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,@PageSizeintAS--替换单引号，避免构造SQL出错set@Fields=replace(@Fields,'''','''''')--要执行的SQL，切分为几个字符串，避免出现长度超过4k时的问题declare@SQL1varchar(4000)declare@SQL2varchar(4000)set@SQL1=''set@SQL2=''if@Whereisnotnullandlen(ltrim(rtrim(@Where)))>0set@Where='where'+@Whereelseset@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+'where1<>1'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@PageIndex<0set@PageIndex=1'set@SQL1=@SQL1+'if@PageIndex>@PageCountand@PageCount>0set@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)>0set@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--输出最终显示结果endelsebegin--****************************************************************************--*************需要创建自增长主键******************************************--****************************************************************************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=@Fieldsset@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@iintdeclare@jintdeclare@iLeftint--左括号的个数declare@iRightint--右括号的个数set@i=0set@j=0set@iLeft=0set@iRight=0set@tmp=''set@TotalFields=''while(len(@Fields)>0)beginset@i=charindex(',',@Fields)--去除字段的表名前缀if(@i=0)begin--找不到逗号分割，即表示只剩下最后一个字段set@tmp=@Fieldsendelsebeginset@tmp=substring(@Fields,1,@i)endset@j=charindex('.',@tmp)if(@j>0)set@tmp=substring(@tmp,@j+1,len(@tmp))--*******当有字段有别名时，只保留字段别名*********--带括号的情况要单独处理，如Convert(varchar(10),B.EndDate,120)asEndDatewhile(charindex('(',@tmp)>0)beginset@iLeft=@iLeft+1set@tmp=substring(@tmp,charindex('(',@tmp)+1,Len(@tmp))endwhile(charindex(')',@tmp)>0)beginset@iRight=@iRight+1set@tmp=substring(@tmp,charindex(')',@tmp)+1,Len(@tmp))end--当括号恰好组队的时候，才能进行字段别名的处理if(@iLeft=@iRight)beginset@iLeft=0set@iRight=0--不对这几个特殊字段作处理:CheckBox、DetailButton、Radioif(charindex('CheckBox',@tmp)=0andcharindex('DetailButton',@tmp)=0andcharindex('Radio',@tmp)=0)begin--判断是否有别名if(charindex('as',@tmp)>0)--别名的第一种写法，带'as'的格式beginset@tmp=substring(@tmp,charindex('as',@tmp)+2,len(@tmp))endelsebeginif(charindex('',@tmp)>0)--别名的第二种写法，带空格("")的格式beginwhile(charindex('',@tmp)>0)beginset@tmp=substring(@tmp,charindex('',@tmp)+1,len(@tmp))endendendendset@TotalFields=@TotalFields+@tmpendif(@i=0)set@Fields=''elseset@Fields=substring(@Fields,@i+1,len(@Fields))end--print@TotalFieldsset@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+@SQL2exec(@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,@PageSizeintASdeclare@Conditionnvarchar(2000)set@Condition=''if(@UserType<>1)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@Conditionexecsp_PagingN'',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'ChlSalesTargetasAleftouterjoinChlSalesTargetPeriodasBonA.TargetPeriod=B.TargetPeriodleftouterjoinChlSalesasConA.Sales=C.SalesCodeleftouterjoinChlItemCatalogasDonA.ItemCatalog=D.CatalogCodeleftouterjoinChlOrgasEonA.OrgID=E.OrgIDleftouterjoinChlOrgasFonC.BranchOrgID=F.OrgID',@Condition,N'A.TargetPerioddesc,C.SalesName,D.CatalogName',@PageIndex,@PageSize注：关于SQL Server通用分页存储过程，源码共享，大家共同完善的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
