但是如果加入其他语句,运行后就会报错。下面是我的sql语句 ALTER Procedure proc_SearchCompanyAll1 @Name varchar(256), @Age varchar(256), @Age1 varchar(256), @Area varchar(256), @Address varchar(256), @Degree varchar(256), @Drive varchar(256), @Experience varchar(256), @JobArea varchar(256), @JobArea1 varchar(256), @Menoy varchar(256), @Public varchar(256), @Sex varchar(256), @WantAddress varchar(256), @WantAddress1 varchar(256), @UpdateDate varchar(256), @SelDegree varchar(256) As declare @strCondition varchar(256) declare @strCondition1 varchar(256) declare @strCondition2 varchar(256) declare @strCondition3 varchar(256) declare @strCondition4 varchar(256) declare @strCondition5 varchar(256) declare @strCondition6 varchar(256) declare @strCondition7 varchar(256) declare @strCondition8 varchar(256) declare @strCondition9 varchar(256) declare @strCondition10 varchar(256) declare @strCondition11 varchar(256) declare @strCondition12 varchar(256) declare @sql nvarchar(500)begin set @sql='select * from Person_stuff where 1=1' +CASE WHEN @Name !='' THEN ' and name = '''+@Name+''''else '' END +CASE WHEN @Sex !='' THEN 'and Sex='+@Sex+''else "" END +CASE WHEN @WantAddress !='-1'THEN 'and expect_post = '''+@WantAddress+''' or expect_post2 = '''+@WantAddress+''' or expect_post3 = '''+@WantAddress+''''else '' END +CASE WHEN @JobArea !='' THEN 'and expect_city='''+@JobArea+''' or expect_city2='''+@JobArea+''' or expect_city3='''+@JobArea+''''else '' END +CASE WHEN @JobArea1 !='' THEN 'and expect_address='''+@JobArea1+''' or expect_address2='''+@JobArea1+''' or expect_address3='''+@JobArea1+''''else '' END +CASE WHEN @Address !='' THEN 'and residence1='''+@Address+'''' else ""END +CASE WHEN @Degree !='' and @SelDegree="1" THEN 'and degree>='''+@Degree+''' ' else '' END +CASE WHEN @Degree !='' and @SelDegree="0" THEN 'and degree='''+@Degree+''' ' else '' END +CASE WHEN @Public !='' THEN 'and polity='''+@Public+''' ' else '' END +CASE WHEN @Menoy !='' THEN 'and expect_menoy='''+@Menoy+'''' else '' END +CASE WHEN @WantAddress1 !='-1' THEN 'and expect_sort='''+@WantAddress1+''' or expect_sort2='''+@WantAddress1+''' or expect_sort3='''+@WantAddress1+'''' else '' END +CASE WHEN @Drive !='' THEN 'and drive_sort='''+@Drive+'''' else '' END +CASE WHEN @Experience !='' THEN 'and job_time>='+@Experience+' 'else '' END +CASE WHEN @Area !='' THEN 'and residence='''+@Area+'''' else '' END +'and age between '+@Age+' and '+@Age1 +CASE WHEN @UpdateDate !='' THEN 'and DateDiff(day,refresh_time,Getdate()) <= '+@UpdateDate+''else '' ENDEXEC sp_executesql @sqlend
ALTER Procedure proc_SearchCompanyAll1 @Name varchar(256), @Age varchar(256), @Age1 varchar(256), @Area varchar(256), @Address varchar(256), @Degree varchar(256), @Drive varchar(256), @Experience varchar(256), @JobArea varchar(256), @JobArea1 varchar(256), @Menoy varchar(256), @Public varchar(256), @Sex varchar(256), @WantAddress varchar(256), @WantAddress1 varchar(256), @UpdateDate varchar(256), @SelDegree varchar(256) As declare @strCondition varchar(256) declare @strCondition1 varchar(256) declare @strCondition2 varchar(256) declare @strCondition3 varchar(256) declare @strCondition4 varchar(256) declare @strCondition5 varchar(256) declare @strCondition6 varchar(256) declare @strCondition7 varchar(256) declare @strCondition8 varchar(256) declare @strCondition9 varchar(256) declare @strCondition10 varchar(256) declare @strCondition11 varchar(256) declare @strCondition12 varchar(256) declare @sql nvarchar(500)begin set @sql='select * from Person_stuff where 1=1' +CASE WHEN @Name !='' THEN ' and name = '''+@Name+''''else '' END +CASE WHEN @Sex !='' THEN ' and Sex='+@Sex+''else "" END +CASE WHEN @WantAddress !='-1'THEN ' and expect_post = '''+@WantAddress+''' or expect_post2 = '''+@WantAddress+''' or expect_post3 = '''+@WantAddress+''''else '' END +CASE WHEN @JobArea !='' THEN ' and expect_city='''+@JobArea+''' or expect_city2='''+@JobArea+''' or expect_city3='''+@JobArea+''''else '' END +CASE WHEN @JobArea1 !='' THEN ' and expect_address='''+@JobArea1+''' or expect_address2='''+@JobArea1+''' or expect_address3='''+@JobArea1+''''else '' END +CASE WHEN @Address !='' THEN ' and residence1='''+@Address+'''' else ""END +CASE WHEN @Degree !='' and @SelDegree="1" THEN ' and degree>='''+@Degree+''' ' else '' END +CASE WHEN @Degree !='' and @SelDegree="0" THEN ' and degree='''+@Degree+''' ' else '' END +CASE WHEN @Public !='' THEN ' and polity='''+@Public+''' ' else '' END +CASE WHEN @Menoy !='' THEN ' and expect_menoy='''+@Menoy+'''' else '' END +CASE WHEN @WantAddress1 !='-1' THEN ' and expect_sort='''+@WantAddress1+''' or expect_sort2='''+@WantAddress1+''' or expect_sort3='''+@WantAddress1+'''' else '' END +CASE WHEN @Drive !='' THEN ' and drive_sort='''+@Drive+'''' else '' END +CASE WHEN @Experience !='' THEN ' and job_time>='+@Experience+' 'else '' END +CASE WHEN @Area !='' THEN ' and residence='''+@Area+'''' else '' END +' and age between '+@Age+' and '+@Age1 +CASE WHEN @UpdateDate !='' THEN ' and DateDiff(day,refresh_time,Getdate()) <= '+@UpdateDate+''else '' ENDEXEC sp_executesql @sqlend先把AND前面空个格子再说,再有问题PRINT出来看看,分析一下就能很快发现问题。
@SelDegree="1" --> @SelDegree='1'
把sql print出来慢慢分析,一般来说是多了'或少'的问题
打印出来正确的,可是就是运行不正确。 declare @sql varchar(8000),@Name varchar(256), @Age varchar(256), @Age1 varchar(256), @Area varchar(256), @Address varchar(256), @Degree varchar(256), @Drive varchar(256), @Experience varchar(256), @JobArea varchar(256), @JobArea1 varchar(256), @Menoy varchar(256), @Public varchar(256), @Sex varchar(256), @WantAddress varchar(256), @WantAddress1 varchar(256), @UpdateDate varchar(256), @SelDegree varchar(256) set @name='DDDD' set @Age='18' set @Age1='60' set @Area='北京' set @Address='直辖市' set @Degree='1' set @Drive='B' set @Experience='26' set @JobArea='直辖市' set @JobArea1='北京' set @Menoy='0200104000' set @Public='2' set @Sex='2' set @WantAddress='7' set @WantAddress1='4' set @UpdateDate='15' set @SelDegree='1' set @sql='select * from Person_stuff where 1=1' +CASE WHEN @Name !='' THEN ' and name = '''+@Name+''''else '' END +CASE WHEN @Sex !='' THEN ' and Sex='''+@Sex+''''else '' END +CASE WHEN @WantAddress !='-1'THEN ' and expect_post = '''+@WantAddress+''' or expect_post2 = '''+@WantAddress+''' or expect_post3 = '''+@WantAddress+''''else '' END +CASE WHEN @JobArea !='' THEN ' and expect_city='''+@JobArea+''' or expect_city2='''+@JobArea+''' or expect_city3='''+@JobArea+''''else '' END +CASE WHEN @JobArea1 !='' THEN ' and expect_address='''+@JobArea1+''' or expect_address2='''+@JobArea1+''' or expect_address3='''+@JobArea1+''''else '' END +CASE WHEN @Address !='' THEN ' and residence1='''+@Address+'''' else '' END +CASE WHEN @Degree !='' and @SelDegree='1' THEN ' and degree>='''+@Degree+''' ' else '' END +CASE WHEN @Degree !='' and @SelDegree='0' THEN ' and degree='''+@Degree+''' ' else '' END +CASE WHEN @Public !='' THEN ' and polity='''+@Public+''' ' else '' END +CASE WHEN @Menoy !='' THEN ' and expect_menoy='''+@Menoy+'''' else '' END +CASE WHEN @WantAddress1 !='-1' THEN ' and expect_sort='''+@WantAddress1+''' or expect_sort2='''+@WantAddress1+''' or expect_sort3='''+@WantAddress1+'''' else '' END +CASE WHEN @Drive !='' THEN ' and drive_sort='''+@Drive+'''' else '' END +CASE WHEN @Experience !='' THEN ' and job_time>='+@Experience+' 'else '' END +CASE WHEN @Area !='' THEN ' and residence='''+@Area+'''' else '' END +CASE WHEN @UpdateDate !='' THEN ' and DateDiff(day,refresh_time,Getdate()) <= '+@UpdateDate+''else '' END +' and age between '''+@Age+''' and '''+@Age1+''''
print @sql 可是在测试这个脚本的时候报错 Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ','. Stored Procedure: rcw.dbo.proc_SearchCompanyAll1 Return Code = 0
select * from Person_stuff where 1=1 and name = 'DDDD' and Sex='2' and expect_post = '7' or expect_post2 = '7' or expect_post3 = '7' and expect_city='直?市' or expect_city2='直?市' or expect_city3='直?市' and expect_address='北京' or expect_address2='北京' or expect_address3='北京' and residence1='直?市' and degree>='1' and polity='2' and expect_menoy='0200104000' and expect_sort='4' or expect_sort2='4' or expect_sort3='4' and drive_sort='B' and job_time>=26 and residence='北京' and DateDiff(day,refresh_time,Getdate()) <= 15 and age between '18' and '60' 在“或”的那些语句上要加括号吧
关系日期的比较日期列不要用UDT效率低用不到索引。
select * from Person_stuff where 1=1 and name = 'DDDD' and Sex='2' and expect_post = '7' or expect_post2 = '7' or expect_post3 = '7' and expect_city='直辖市' or expect_city2='直辖市' or expect_city3='直辖市' and expect_address='北京' or expect_address2='北京' or expect_address3='北京' and residence1='直辖市' and degree>='1' and polity='2' and expect_menoy='0200104000' --0开头的menoy? and expect_sort='4' or expect_sort2='4' or expect_sort3='4' and drive_sort='B' and job_time>=26 --time>=26? and residence='北京' and DateDiff(day,refresh_time,Getdate()) <= 15 and age between '18' and '60'--or和and是否有错误?缺少括号吗?
print '>30'
ALTER Procedure proc_SearchCompanyAll1
@Name varchar(256),
@Age varchar(256),
@Age1 varchar(256),
@Area varchar(256),
@Address varchar(256),
@Degree varchar(256),
@Drive varchar(256),
@Experience varchar(256),
@JobArea varchar(256),
@JobArea1 varchar(256),
@Menoy varchar(256),
@Public varchar(256),
@Sex varchar(256),
@WantAddress varchar(256),
@WantAddress1 varchar(256),
@UpdateDate varchar(256),
@SelDegree varchar(256)
As
declare @strCondition varchar(256)
declare @strCondition1 varchar(256)
declare @strCondition2 varchar(256)
declare @strCondition3 varchar(256)
declare @strCondition4 varchar(256)
declare @strCondition5 varchar(256)
declare @strCondition6 varchar(256)
declare @strCondition7 varchar(256)
declare @strCondition8 varchar(256)
declare @strCondition9 varchar(256)
declare @strCondition10 varchar(256)
declare @strCondition11 varchar(256)
declare @strCondition12 varchar(256)
declare @sql nvarchar(500)begin
set @sql='select * from Person_stuff where 1=1'
+CASE WHEN @Name !='' THEN ' and name = '''+@Name+''''else '' END
+CASE WHEN @Sex !='' THEN 'and Sex='+@Sex+''else "" END
+CASE WHEN @WantAddress !='-1'THEN 'and expect_post = '''+@WantAddress+''' or expect_post2 = '''+@WantAddress+''' or expect_post3 = '''+@WantAddress+''''else '' END
+CASE WHEN @JobArea !='' THEN 'and expect_city='''+@JobArea+''' or expect_city2='''+@JobArea+''' or expect_city3='''+@JobArea+''''else '' END
+CASE WHEN @JobArea1 !='' THEN 'and expect_address='''+@JobArea1+''' or expect_address2='''+@JobArea1+''' or expect_address3='''+@JobArea1+''''else '' END
+CASE WHEN @Address !='' THEN 'and residence1='''+@Address+'''' else ""END
+CASE WHEN @Degree !='' and @SelDegree="1" THEN 'and degree>='''+@Degree+''' ' else '' END
+CASE WHEN @Degree !='' and @SelDegree="0" THEN 'and degree='''+@Degree+''' ' else '' END
+CASE WHEN @Public !='' THEN 'and polity='''+@Public+''' ' else '' END
+CASE WHEN @Menoy !='' THEN 'and expect_menoy='''+@Menoy+'''' else '' END
+CASE WHEN @WantAddress1 !='-1' THEN 'and expect_sort='''+@WantAddress1+''' or expect_sort2='''+@WantAddress1+''' or expect_sort3='''+@WantAddress1+'''' else '' END
+CASE WHEN @Drive !='' THEN 'and drive_sort='''+@Drive+'''' else '' END
+CASE WHEN @Experience !='' THEN 'and job_time>='+@Experience+' 'else '' END
+CASE WHEN @Area !='' THEN 'and residence='''+@Area+'''' else '' END
+'and age between '+@Age+' and '+@Age1
+CASE WHEN @UpdateDate !='' THEN 'and DateDiff(day,refresh_time,Getdate()) <= '+@UpdateDate+''else '' ENDEXEC sp_executesql @sqlend
@Name varchar(256),
@Age varchar(256),
@Age1 varchar(256),
@Area varchar(256),
@Address varchar(256),
@Degree varchar(256),
@Drive varchar(256),
@Experience varchar(256),
@JobArea varchar(256),
@JobArea1 varchar(256),
@Menoy varchar(256),
@Public varchar(256),
@Sex varchar(256),
@WantAddress varchar(256),
@WantAddress1 varchar(256),
@UpdateDate varchar(256),
@SelDegree varchar(256)
As
declare @strCondition varchar(256)
declare @strCondition1 varchar(256)
declare @strCondition2 varchar(256)
declare @strCondition3 varchar(256)
declare @strCondition4 varchar(256)
declare @strCondition5 varchar(256)
declare @strCondition6 varchar(256)
declare @strCondition7 varchar(256)
declare @strCondition8 varchar(256)
declare @strCondition9 varchar(256)
declare @strCondition10 varchar(256)
declare @strCondition11 varchar(256)
declare @strCondition12 varchar(256)
declare @sql nvarchar(500)begin
set @sql='select * from Person_stuff where 1=1'
+CASE WHEN @Name !='' THEN ' and name = '''+@Name+''''else '' END
+CASE WHEN @Sex !='' THEN ' and Sex='+@Sex+''else "" END
+CASE WHEN @WantAddress !='-1'THEN ' and expect_post = '''+@WantAddress+''' or expect_post2 = '''+@WantAddress+''' or expect_post3 = '''+@WantAddress+''''else '' END
+CASE WHEN @JobArea !='' THEN ' and expect_city='''+@JobArea+''' or expect_city2='''+@JobArea+''' or expect_city3='''+@JobArea+''''else '' END
+CASE WHEN @JobArea1 !='' THEN ' and expect_address='''+@JobArea1+''' or expect_address2='''+@JobArea1+''' or expect_address3='''+@JobArea1+''''else '' END
+CASE WHEN @Address !='' THEN ' and residence1='''+@Address+'''' else ""END
+CASE WHEN @Degree !='' and @SelDegree="1" THEN ' and degree>='''+@Degree+''' ' else '' END
+CASE WHEN @Degree !='' and @SelDegree="0" THEN ' and degree='''+@Degree+''' ' else '' END
+CASE WHEN @Public !='' THEN ' and polity='''+@Public+''' ' else '' END
+CASE WHEN @Menoy !='' THEN ' and expect_menoy='''+@Menoy+'''' else '' END
+CASE WHEN @WantAddress1 !='-1' THEN ' and expect_sort='''+@WantAddress1+''' or expect_sort2='''+@WantAddress1+''' or expect_sort3='''+@WantAddress1+'''' else '' END
+CASE WHEN @Drive !='' THEN ' and drive_sort='''+@Drive+'''' else '' END
+CASE WHEN @Experience !='' THEN ' and job_time>='+@Experience+' 'else '' END
+CASE WHEN @Area !='' THEN ' and residence='''+@Area+'''' else '' END
+' and age between '+@Age+' and '+@Age1
+CASE WHEN @UpdateDate !='' THEN ' and DateDiff(day,refresh_time,Getdate()) <= '+@UpdateDate+''else '' ENDEXEC sp_executesql @sqlend先把AND前面空个格子再说,再有问题PRINT出来看看,分析一下就能很快发现问题。
-->
@SelDegree='1'
declare @sql varchar(8000),@Name varchar(256),
@Age varchar(256),
@Age1 varchar(256),
@Area varchar(256),
@Address varchar(256),
@Degree varchar(256),
@Drive varchar(256),
@Experience varchar(256),
@JobArea varchar(256),
@JobArea1 varchar(256),
@Menoy varchar(256),
@Public varchar(256),
@Sex varchar(256),
@WantAddress varchar(256),
@WantAddress1 varchar(256),
@UpdateDate varchar(256),
@SelDegree varchar(256)
set @name='DDDD'
set @Age='18'
set @Age1='60'
set @Area='北京'
set @Address='直辖市'
set @Degree='1'
set @Drive='B'
set @Experience='26'
set @JobArea='直辖市'
set @JobArea1='北京'
set @Menoy='0200104000'
set @Public='2'
set @Sex='2'
set @WantAddress='7'
set @WantAddress1='4'
set @UpdateDate='15'
set @SelDegree='1'
set @sql='select * from Person_stuff where 1=1'
+CASE WHEN @Name !='' THEN ' and name = '''+@Name+''''else '' END
+CASE WHEN @Sex !='' THEN ' and Sex='''+@Sex+''''else '' END
+CASE WHEN @WantAddress !='-1'THEN ' and expect_post = '''+@WantAddress+''' or expect_post2 = '''+@WantAddress+''' or expect_post3 = '''+@WantAddress+''''else '' END
+CASE WHEN @JobArea !='' THEN ' and expect_city='''+@JobArea+''' or expect_city2='''+@JobArea+''' or expect_city3='''+@JobArea+''''else '' END
+CASE WHEN @JobArea1 !='' THEN ' and expect_address='''+@JobArea1+''' or expect_address2='''+@JobArea1+''' or expect_address3='''+@JobArea1+''''else '' END
+CASE WHEN @Address !='' THEN ' and residence1='''+@Address+'''' else '' END
+CASE WHEN @Degree !='' and @SelDegree='1' THEN ' and degree>='''+@Degree+''' ' else '' END
+CASE WHEN @Degree !='' and @SelDegree='0' THEN ' and degree='''+@Degree+''' ' else '' END
+CASE WHEN @Public !='' THEN ' and polity='''+@Public+''' ' else '' END
+CASE WHEN @Menoy !='' THEN ' and expect_menoy='''+@Menoy+'''' else '' END
+CASE WHEN @WantAddress1 !='-1' THEN ' and expect_sort='''+@WantAddress1+''' or expect_sort2='''+@WantAddress1+''' or expect_sort3='''+@WantAddress1+'''' else '' END
+CASE WHEN @Drive !='' THEN ' and drive_sort='''+@Drive+'''' else '' END
+CASE WHEN @Experience !='' THEN ' and job_time>='+@Experience+' 'else '' END
+CASE WHEN @Area !='' THEN ' and residence='''+@Area+'''' else '' END
+CASE WHEN @UpdateDate !='' THEN ' and DateDiff(day,refresh_time,Getdate()) <= '+@UpdateDate+''else '' END
+' and age between '''+@Age+''' and '''+@Age1+''''
print @sql
可是在测试这个脚本的时候报错
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
Stored Procedure: rcw.dbo.proc_SearchCompanyAll1
Return Code = 0
select *
from Person_stuff
where 1=1
and name = 'DDDD'
and Sex='2'
and expect_post = '7'
or expect_post2 = '7'
or expect_post3 = '7'
and expect_city='直辖市'
or expect_city2='直辖市'
or expect_city3='直辖市'
and expect_address='北京'
or expect_address2='北京'
or expect_address3='北京'
and residence1='直辖市'
and degree>='1'
and polity='2'
and expect_menoy='0200104000' --0开头的menoy?
and expect_sort='4'
or expect_sort2='4'
or expect_sort3='4'
and drive_sort='B'
and job_time>=26 --time>=26?
and residence='北京'
and DateDiff(day,refresh_time,Getdate()) <= 15
and age between '18' and '60'--or和and是否有错误?缺少括号吗?