世家协同完备,模糊查询

在大家做寻找的时候日常要用到模糊查询
(注:此中name1,name2,name3,name4为数据库字段卡塔尔国 1.方法 复制代码 代码如下:sql=”select*fromtablewhere”
ifname””then sql=sql&”name1='”&变量&”‘” endif ifbelong1″”then
sql=sql&”name2='”&变量&”‘” endif ifbelong2″”then
sql=sql&”name3='”&变量&”‘” endif ifbelong3″”then
sql=sql&”name4='”&变量&”‘” endif 2.办法(存款和储蓄进度State of Qatar 复制代码 代码如下:createprocspyourname (
@name1varchar(20卡塔尔(قطر‎, @name2varchar(20卡塔尔国, @name3varchar(20卡塔尔国,
@name4varchar(20State of Qatar, @name5varchar(20State of Qatar 卡塔尔国 WITHENCRYPTION as setnocounton
declare@SQLvarchar(5000)set@SQL=’selectname1,name2,name3,name4,name5fromyourtablewhere1=1′
if@name” set@SQL=@SQL+’andname1like”%’+@name+’%”’ if@belong1”
set@SQL=@SQL+’andname2like”%’+@belong1+’%”’ if@belong2”
set@SQL=@SQL+’andname3like”%’+@belong2+’%”’ if@belong3”
set@SQL=@SQL+’andname4like”%’+@belong3+’%”’ exec(@SQL卡塔尔

漫漫未有上来写点东西了,后天无独有偶有空,分享一些私人民居房体会,就是有关分页的积累进程,那几个主题材料应当是老生重谈了,网络的通用存款和储蓄进程的品类已经够多了,不过,好象见到的多数不能满足一些目迷五色的SQL语句的分页,比方下边那句:
select”asCheckBox,A.TargetID,A.TargetPeriod,Convert(varchar(10卡塔尔(قطر‎,B.BeginDate,120State of QatarasBeginDate,
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
leftouterjoinChlItemCatalo瓦斯DonA.ItemCatalog=D.CatalogCode
leftouterjoinChlOr瓦斯EonA.OrgID=E.OrgID
leftouterjoinChlOrgasFonC.BranchOrgID=F.OrgID
whereA.TargetPeriod=’二〇〇五07’andA.TargetPeriod=’二零零五08’andF.OrgCodelike’%123%’andE.OrgCodelike’%123%’
orderbyA.TargetPerioddesc,C.SalesName,D.CatalogName上边那句SQL里面有部分出奇意况,举个例子选取了Convert函数,并且从不主键,有多表连接,有表别称,字段别称等等,那一个意况管理起来恐怕相比棘手,当然,此中的“”asCheck博克斯”是自个儿系统个中的特例处境,用来做一些拍卖的。
笔者这里提供二个和好开销的通用分页存款和储蓄进程,有何好的提议和观点,大家请多都赐教。代码如下:

文章轻巧的牵线有关SQL Server 导出 insert
into的措施有供给的对象能够参谋一下。 代码如下复制代码 Create PROCEDU本田CR-VEdbo.UspOutputData @tablename sysname AS declare @column varchar(1000)declare @columndata varchar(1000卡塔尔 declare @sql varchar(4000卡塔尔国 declare
@xtype tinyint declare @name sysname declare @objectId int declare
@objectname sysname declare @ident int set nocount on set
@objectId=object_id(@tablename卡塔尔国 if @objectId is null —
判??ο笫欠翊嬖?nbsp; begin print ‘The object not exists’ return end set
@objectname=object_name(@objectId卡塔尔 if @objectname is null or
charindex(@objectname,@tablename卡塔尔=0 –此推断不连贯 begin print ‘object
not in current database’ return end if
OBJECTPROPERTY(@objectId,’IsTable’State of Qatar 1 — 判??ο笫欠袷?able begin print
‘The object is not table’ return end select @ident=status0x80 from
syscolumns where id=@objectid and status0x80=0x80 if @ident is not null
print ‘SET IDENTITY_Insert ‘+@TableName+’ ON’ declare
syscolumns_cursor cursor for select c.name,c.xtype from syscolumns c
where c.id=@objectid order by c.colid open syscolumns_cursor set
@column=” set @columndata=” fetch next from syscolumns_cursor into
@name,@xtype while @@fetch_status -1 begin if @@fetch_status-2 begin
if @xtype not in(189,34,35,99,98State of Qatar–timestamp不需处理,image,text,ntext,sql_variant 临时不管理 begin set
@column=@column+case when len(@column卡塔尔(قطر‎=0 then” else ‘,’end+@name set
@columndata=@columndata+case when len(@columndata卡塔尔=0 then ” else
‘,”,”,’end +case when @xtype in(167,175卡塔尔国 then
””””’+’+@name+’+””””’ –varchar,char when @xtype in(231,239卡塔尔国then ”’N”””+’+@name+’+””””’ –nvarchar,nchar when @xtype=61 then
””””’+convert(char(23卡塔尔(قطر‎,’+@name+’,121卡塔尔国+””””’ –datetime when
@xtype=58 then ””””’+convert(char(16卡塔尔,’+@name+’,120State of Qatar+””””’
–smalldatetime when @xtype=36 then
””””’+convert(char(36卡塔尔(قطر‎,’+@name+’卡塔尔(قطر‎+””””’ –uniqueidentifier else
@name end end end fetch next from syscolumns_cursor into @name,@xtype
end close syscolumns_北京pk赛车官网下载,cursor deallocate syscolumns_cursor set @sql=’set
nocount on select ”insert ‘+@tablename+'(‘+@column+’) values(”as
”–”,’+@columndata+’,”)” from ‘+@tablename print ‘–‘+@sql
exec(@sql) if @ident is not null print ‘SET IDENTITY_Insert
‘+@TableName+’ OFF’ GO 施行措施是: exec UspOutputData 你的表名
获得导出数据的话语,但image,text,ntext,sql_variant
列不出未来讲话,今后改过。

通用分页存款和储蓄进度—-Sp_Paging /**//*

效果:通用分页存储进程 参数:
@PKvarchar(50卡塔尔(قطر‎,主键,用来排序的单一字段,空的话,表示从未主键,存储进程将自行创制标志列主键
@Fieldsvarchar(500卡塔尔,要出示的字段列表(格式如:ID,Code,Name)@Tablesvarchar(1000卡塔尔,要运用的表集结(Org卡塔尔国@Wherevarchar(500卡塔尔(قطر‎,查询条件(Codelike’100’State of Qatar@OrderByvarchar(100State of Qatar,排序条件(支持三个排序字段,如:ID,Codedesc,Namedesc卡塔尔(قطر‎@PageIndexint,当前要出示的页的页索引,索引从1发轫,无记录时为0。
@PageSizeint,页大小 创设者:HollisYao 成立日期:二〇〇六-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卡塔尔国卡塔尔国State of Qatar0
set@Where=’where’+@Where else set@Where=’where1=1′
set@SQL1=@SQL1+’declare@TotalCountint’–声明一个变量,总记录数
set@SQL1=@SQL1+’declare@PageCountint’–声美赞臣(Meadjohnson卡塔尔个变量,总页数
set@SQL1=@SQL1+’declare@PageIndexint’–声飞鹤(Nutrilon卡塔尔(قطر‎(Dumex卡塔尔国个变量,页索引
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,@PageSizeState of Qatar+’-1卡塔尔/’+convert(varchar,@PageSize卡塔尔(قطر‎–获取总页数
set@SQL1=@SQL1+’set@PageIndex=’+convert(varchar,@PageIndexState of Qatar–设置科学的页索引
set@SQL1=@SQL1+’if@PageIndex0set@PageIndex=1′
set@SQL1=@SQL1+’if@PageIndex@PageCountand@PageCount0set@PageIndex=@PageCount’
set@SQL1=@SQL1+’set@StartRow=(@PageIndex-1State of Qatar*’+convert(varchar,@PageSize)+’+1′
if(charindex(‘,’,@OrderBy)=0andcharindex(@PK,@OrderBy)0) begin
–****************************************************************************
–****************没有必要成立主键********************************************
–****************************************************************************
declare@SortDirectionvarchar(10State of Qatar–排序方向,=:升序,=:倒序
set@SortDirection=’=’ ifcharindex(‘desc’,@OrderByState of Qatar0
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,@PageSizeState of Qatar–设置再次来到记录数为页大小
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’–声澳优(Ausnutria HyprocaState of Qatar个变量,截止索引
set@SQL2=@SQL2+’set@PKBegin=@StartRow’
set@SQL2=@SQL2+’set@PKEnd=@EndRow’
–****************************************************************************
–************对卓殊字段实行调换,以便可以插入到一时表******************
–****************************************************************************
declare@TempFieldsvarchar(500State of Qatar set@TempFields=@Fields
set@TempFields=replace(@TempFields,””’asCheckBox’,”卡塔尔(قطر‎set@TempFields=replace(@TempFields,””’asDetailButton’,”)set@TempFields=replace(@TempFields,””’asRadio’,”)set@TempFields=LT汉兰达IM(RTPRADOIM(@TempFields卡塔尔(قطر‎卡塔尔ifleft(@TempFields,1卡塔尔(قطر‎=’,’–去除最侧边的逗号
set@TempFields=substring(@TempFields,2,len(@TempFields卡塔尔卡塔尔国ifright(@TempFields,1卡塔尔=’,’–去除最侧面的逗号
set@TempFields=substring(@TempFields,1,len(@TempFields卡塔尔国-1State of Qatarset@SQL2=@SQL2+’selectidentity(int,1,1State of QatarasPK,’+@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(‘.’,@tmpState of Qatarif(@j0State of Qatar set@tmp=substring(@tmp,@j+1,len(@tmpState of Qatar)–*******当有字段有别称时,只保留字段小名*********
–带括号的情形要独立处理,如Convert(varchar(10State of Qatar,B.EndDate,120State of QatarasEndDate
while(charindex(‘(‘,@tmp卡塔尔(قطر‎0卡塔尔 begin set@iLeft=@iLeft+1
set@tmp=substring(@tmp,charindex(‘(‘,@tmp卡塔尔(قطر‎+1,Len(@tmp卡塔尔国卡塔尔 end
while(charindex(‘State of Qatar’,@tmp卡塔尔国0卡塔尔 begin set@iRight=@iRight+1
set@tmp=substring(@tmp,charindex(‘State of Qatar’,@tmpState of Qatar+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(@tmpState of Qatar卡塔尔(قطر‎ end else begin
if(charindex(”,@tmp卡塔尔国0卡塔尔国–外号的第二种写法,带空格(“”卡塔尔(قطر‎的格式 begin
while(charindex(”,@tmpState of Qatar0卡塔尔(قطر‎ begin
set@tmp=substring(@tmp,charindex(”,@tmp卡塔尔国+1,len(@tmpState of Qatar卡塔尔国 end end end end
set@TotalFields=@TotalFields+@tmp end if(@i=0卡塔尔国 set@Fields=” else
set@Fields=substring(@菲尔德s,@i+1,len(@Fields卡塔尔(قطر‎State of Qatar 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,@PageSizeState of Qatar+’asPageSize,@TotalCountasTotalCount’
–print@SQL1+@SQL2 exec(@SQL1+@SQL2卡塔尔借使采纳那一个通用分页存款和储蓄进度的话,那么调用方法如下:

利用通用分页存储进度举行分页 /**//*

效果与利益:获取发卖目的,依照条件 参数: @UserTypeint, @OrgIDvarchar(500卡塔尔,
@TargetPeriodBeginnvarchar(50卡塔尔, @TargetPeriodEndnvarchar(50卡塔尔国,
@BranchOrgCodenvarchar(50卡塔尔国, @BranchOrgNamenvarchar(50State of Qatar,
@OrgCodenvarchar(50), @OrgNamenvarchar(50State of Qatar, @SalesCodenvarchar(50卡塔尔,
@SalesNamenvarchar(50State of Qatar, @CatalogCodenvarchar(50State of Qatar,
@CatalogNamenvarchar(50卡塔尔(قطر‎,
@PageIndexint,当前要出示的页的页索引,索引从1上马,无记录时为0。
@PageSizeint,页大小 创造者:霍LissYao 创制日期:二〇〇七-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

发表评论

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