,@hospitalname varchar(20)='' ,@doctorname nvarchar(20)='' ,@proxyname varchar(20)='' ,@proxyphone varchar(20) ='' ,@proxyemail varchar(20) ='' declare @tsql varchar(600) set @citycode='210100' set @hospitalname ='' set @doctorname='陈'
select @tsql='select * from rsi_sampleinfo_yezi where 1=1 ' +case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end +case when @hospitalname<>'' then ' and hospitalname=''%'+@hospitalname+'%' else '' end +case when @doctorname<>'' then ' and doctorname=''%'+@doctorname+'%' else '' end +case when @proxyname<>'' then ' and proxyname=''%'+@proxyname+'%' else '' end +case when @proxyphone<>'' then ' and proxyphone=''%'+@proxyphone+'%' else '' end +case when @proxyemail<>'' then ' and proxyemail=''%'+@proxyemail+'%' else '' end
,@hospitalname varchar(20)='' ,@doctorname nvarchar(20)='' ,@proxyname varchar(20)='' ,@proxyphone varchar(20) ='' ,@proxyemail varchar(20) ='' declare @tsql varchar(600) set @citycode='210100' set @hospitalname ='' set @doctorname='陈'
select @tsql='select * from rsi_sampleinfo_yezi where 1=1 ' +case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end +case when @hospitalname<>'' then ' and hospitalname=''%'+@hospitalname+'%' else '' end +case when @doctorname<>'' then ' and doctorname=''%'+@doctorname+'%''' else '' end +case when @proxyname<>'' then ' and proxyname=''%'+@proxyname+'%' else '' end +case when @proxyphone<>'' then ' and proxyphone=''%'+@proxyphone+'%' else '' end +case when @proxyemail<>'' then ' and proxyemail=''%'+@proxyemail+'%' else '' end
,@hospitalname varchar(20)='' ,@doctorname nvarchar(20)='' ,@proxyname varchar(20)='' ,@proxyphone varchar(20) ='' ,@proxyemail varchar(20) ='' declare @tsql varchar(600) set @citycode='210100' set @hospitalname ='' set @doctorname='陈'
select @tsql='select * from rsi_sampleinfo_yezi where 1=1 ' +case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end +case when @hospitalname<>'' then ' and hospitalname=''%'+@hospitalname+'%' else '' end +case when @doctorname<>'' then ' and doctorname=''%'+@doctorname+'%''' else '' end +case when @proxyname<>'' then ' and proxyname=''%'+@proxyname+'%''' else '' end +case when @proxyphone<>'' then ' and proxyphone=''%'+@proxyphone+'%''' else '' end +case when @proxyemail<>'' then ' and proxyemail=''%'+@proxyemail+'%''' else '' end
set @citycode='210100' set @hospitalname ='' set @doctorname='陈'
select @tsql='select * from rsi_sampleinfo_yezi where 1=1 ' +case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end +case when @hospitalname<>'' then ' and hospitalname=''%'+@hospitalname+'%' else '' end +case when @doctorname<>'' then ' and doctorname=''%'+@doctorname+'%'' ' else '' end +case when @proxyname<>'' then ' and proxyname=''%'+@proxyname+'%' else '' end +case when @proxyphone<>'' then ' and proxyphone=''%'+@proxyphone+'%' else '' end +case when @proxyemail<>'' then ' and proxyemail=''%'+@proxyemail+'%' else '' end
exec( @tsql)
有 > 这种符号么 必须有。 declare @citycode varchar(20)='' ,@areacode varchar(20) ='' ,@hospitalname varchar(20) ='' ,@doctorname varchar(20) ='' ,@proxyname varchar(20) ='' ,@proxyphone varchar(20) ='' ,@proxyemail varchar(20) ='' --设定条件查询语句 declare @search_str varchar(1000) set @search_str= case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end +case when @hospitalname<>'' then ' and rsi_hospital like ''%'+@hospitalname+'%''' else '' end +case when @doctorname<>'' then ' and rsi_doctor like ''%'+@doctorname+'%'' ' else '' end +case when @proxyname<>'' then ' and rsi_proxy like ''%'+@proxyname+'%''' else '' end +case when @proxyphone<>'' then ' and rsi_proxyphone like ''%'+@proxyphone+'%''' else '' end +case when @proxyemail<>'' then ' and rsi_proxyemail like ''%'+@proxyemail+'%''' else '' end +case when @citycode<>'' then ' and rsi_regioncode ='''+@citycode+'''' else '' end select @tsql6 ='select distinct rsi_doctorcode from rsi_sampleinfo_yezi where 1=1 ' set @tsql6=@tsql6+@search_str select distinct b.rsi_areaname,b.rsi_region,a.wp_ACNid,b.rsi_hospital as rsi_shopname,b.rsi_doctorcode,b.rsi_doctor,b.rsi_proxycode,b.rsi_proxy,b.rsi_proxy_phone,b.rsi_proxy_email,a.wp_contact_num,a.wp_contact_num_success,a.wp_num2,a.wp_quota,a.completeschedule,a.completeschedulestatus from wp_writepaper_schedule a join rsi_sampleinfo_yezi b on a.wp_AcnID=b.rsi_doctorcode and a.wp_ACNid in (@tsql6) 经过修改 和独立测试 , select distinct b.rsi_areaname,b.rsi_region,a.wp_ACNid,b.rsi_hospital as rsi_shopname,b.rsi_doctorcode,b.rsi_doctor,b.rsi_proxycode,b.rsi_proxy,b.rsi_proxy_phone,b.rsi_proxy_email,a.wp_contact_num,a.wp_contact_num_success,a.wp_num2,a.wp_quota,a.completeschedule,a.completeschedulestatus from wp_writepaper_schedule a join rsi_sampleinfo_yezi b on a.wp_AcnID=b.rsi_doctorcode
你说这句?单纯看语句没问题,如果有问题可能在on的条件那里,没有匹配的数据,改成left join试试 SELECT DISTINCT b.rsi_areaname , b.rsi_region , a.wp_ACNid , b.rsi_hospital AS rsi_shopname , b.rsi_doctorcode , b.rsi_doctor , b.rsi_proxycode , b.rsi_proxy , b.rsi_proxy_phone , b.rsi_proxy_email , a.wp_contact_num , a.wp_contact_num_success , a.wp_num2 , a.wp_quota , a.completeschedule , a.completeschedulestatus FROM wp_writepaper_schedule a JOIN rsi_sampleinfo_yezi b ON a.wp_AcnID = b.rsi_doctorcode
select distinct b.rsi_areaname,b.rsi_region,a.wp_ACNid,b.rsi_hospital as rsi_shopname,b.rsi_doctorcode,b.rsi_doctor,b.rsi_proxycode,b.rsi_proxy,b.rsi_proxy_phone,b.rsi_proxy_email,a.wp_contact_num,a.wp_contact_num_success,a.wp_num2,a.wp_quota,a.completeschedule,a.completeschedulestatus from wp_writepaper_schedule a join rsi_sampleinfo_yezi b on a.wp_AcnID=b.rsi_doctorcode and a.wp_ACNid in (@tsql6)
select distinct b.rsi_areaname,b.rsi_region,a.wp_ACNid,b.rsi_hospital as rsi_shopname,b.rsi_doctorcode,b.rsi_doctor,b.rsi_proxycode,b.rsi_proxy,b.rsi_proxy_phone,b.rsi_proxy_email,a.wp_contact_num,a.wp_contact_num_success,a.wp_num2,a.wp_quota,a.completeschedule,a.completeschedulestatus from wp_writepaper_schedule a join rsi_sampleinfo_yezi b on a.wp_AcnID=b.rsi_doctorcode and a.wp_ACNid in (@tsql6) 这句,你可以看下我11楼的,我很是奇怪联合起来竟然看不了数据,因为分别查过是有数据的
你这个要用拼接,不能直接in一个变量,特别是这种字符窜declare @sql varchar(max) set @sql='select distinct b.rsi_areaname,b.rsi_region,a.wp_ACNid,b.rsi_hospital as rsi_shopname,b.rsi_doctorcode,b.rsi_doctor,b.rsi_proxycode,b.rsi_proxy,b.rsi_proxy_phone,b.rsi_proxy_email,a.wp_contact_num,a.wp_contact_num_success,a.wp_num2,a.wp_quota,a.completeschedule,a.completeschedulestatus from wp_writepaper_schedule a join rsi_sampleinfo_yezi b on a.wp_AcnID=b.rsi_doctorcode and a.wp_ACNid in '+(''+@tsql6+'') exec (@sql)类似这样,出门上班去,等F版主来看吧
DECLARE
@citycode varchar(20)
,@areacode varchar(20)=''
,@hospitalname varchar(20)=''
,@doctorname nvarchar(20)=''
,@proxyname varchar(20)=''
,@proxyphone varchar(20) =''
,@proxyemail varchar(20) =''
declare @tsql varchar(600)
set @citycode='210100'
set @hospitalname =''
set @doctorname='陈'
select @tsql='select * from rsi_sampleinfo_yezi where 1=1 '
+case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end
+case when @hospitalname<>'' then ' and hospitalname=''%'+@hospitalname+'%' else '' end
+case when @doctorname<>'' then ' and doctorname=''%'+@doctorname+'%' else '' end
+case when @proxyname<>'' then ' and proxyname=''%'+@proxyname+'%' else '' end
+case when @proxyphone<>'' then ' and proxyphone=''%'+@proxyphone+'%' else '' end
+case when @proxyemail<>'' then ' and proxyemail=''%'+@proxyemail+'%' else '' end
exec( @tsql)
报错
字符串 '%陈%' 后的引号不完整。'%陈%' 附近有语法错误。
@citycode varchar(20)
,@areacode varchar(20)=''
,@hospitalname varchar(20)=''
,@doctorname nvarchar(20)=''
,@proxyname varchar(20)=''
,@proxyphone varchar(20) =''
,@proxyemail varchar(20) =''
declare @tsql varchar(600)
set @citycode='210100'
set @hospitalname =''
set @doctorname='陈'
select @tsql='select * from rsi_sampleinfo_yezi where 1=1 '
+case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end
+case when @hospitalname<>'' then ' and hospitalname=''%'+@hospitalname+'%' else '' end
+case when @doctorname<>'' then ' and doctorname=''%'+@doctorname+'%''' else '' end
+case when @proxyname<>'' then ' and proxyname=''%'+@proxyname+'%' else '' end
+case when @proxyphone<>'' then ' and proxyphone=''%'+@proxyphone+'%' else '' end
+case when @proxyemail<>'' then ' and proxyemail=''%'+@proxyemail+'%' else '' end
PRINT( @tsql)
@citycode varchar(20)
,@areacode varchar(20)=''
,@hospitalname varchar(20)=''
,@doctorname nvarchar(20)=''
,@proxyname varchar(20)=''
,@proxyphone varchar(20) =''
,@proxyemail varchar(20) =''
declare @tsql varchar(600)
set @citycode='210100'
set @hospitalname =''
set @doctorname='陈'
select @tsql='select * from rsi_sampleinfo_yezi where 1=1 '
+case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end
+case when @hospitalname<>'' then ' and hospitalname=''%'+@hospitalname+'%' else '' end
+case when @doctorname<>'' then ' and doctorname=''%'+@doctorname+'%''' else '' end
+case when @proxyname<>'' then ' and proxyname=''%'+@proxyname+'%''' else '' end
+case when @proxyphone<>'' then ' and proxyphone=''%'+@proxyphone+'%''' else '' end
+case when @proxyemail<>'' then ' and proxyemail=''%'+@proxyemail+'%''' else '' end
EXEC( @tsql)
@proxyphone VARCHAR(50),@proxyemail VARCHAR(50)
SELECT @tsql='select distinct rsi_doctorcode from rsi_sampleinfo_yezi where 1=1 '
IF @areacode>''
SET @tsql=@tsql+' and rsi_area=@areacode'
IF @hospitalname>''
SET @tsql=@tsql+' and hospitalname=@hospitalname'
...--中间的自己去写
EXEC sp_executesql @tSql
,N'@areacode VARCHAR(50), @hospitalname VARCHAR(50),@doctorname VARCHAR(50),@proxyname VARCHAR(50),@proxyphone VARCHAR(50),@proxyemail VARCHAR(50)'
,@areacode , @hospitalname ,@doctorname ,@proxyname ,@proxyphone ,@proxyemail
declare
@citycode varchar(20)
,@areacode varchar(20)=''
,@hospitalname varchar(20)=''
,@doctorname nvarchar(20)=''
,@proxyname varchar(20)=''
,@proxyphone varchar(20) =''
,@proxyemail varchar(20) =''declare @tsql varchar(600)
set @citycode='210100'
set @hospitalname =''
set @doctorname='陈'
select @tsql='select * from rsi_sampleinfo_yezi where 1=1 '
+case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end
+case when @hospitalname<>'' then ' and hospitalname=''%'+@hospitalname+'%' else '' end
+case when @doctorname<>'' then ' and doctorname=''%'+@doctorname+'%'' ' else '' end
+case when @proxyname<>'' then ' and proxyname=''%'+@proxyname+'%' else '' end
+case when @proxyphone<>'' then ' and proxyphone=''%'+@proxyphone+'%' else '' end
+case when @proxyemail<>'' then ' and proxyemail=''%'+@proxyemail+'%' else '' end
exec( @tsql)
必须有。 declare @citycode varchar(20)=''
,@areacode varchar(20) =''
,@hospitalname varchar(20) =''
,@doctorname varchar(20) =''
,@proxyname varchar(20) =''
,@proxyphone varchar(20) =''
,@proxyemail varchar(20) =''
--设定条件查询语句
declare @search_str varchar(1000)
set @search_str= case when @areacode<>'' then ' and rsi_area='''+@areacode+'' else '' end
+case when @hospitalname<>'' then ' and rsi_hospital like ''%'+@hospitalname+'%''' else '' end
+case when @doctorname<>'' then ' and rsi_doctor like ''%'+@doctorname+'%'' ' else '' end
+case when @proxyname<>'' then ' and rsi_proxy like ''%'+@proxyname+'%''' else '' end
+case when @proxyphone<>'' then ' and rsi_proxyphone like ''%'+@proxyphone+'%''' else '' end
+case when @proxyemail<>'' then ' and rsi_proxyemail like ''%'+@proxyemail+'%''' else '' end
+case when @citycode<>'' then ' and rsi_regioncode ='''+@citycode+'''' else '' end select @tsql6 ='select distinct rsi_doctorcode from rsi_sampleinfo_yezi where 1=1 '
set @tsql6=@tsql6+@search_str select distinct b.rsi_areaname,b.rsi_region,a.wp_ACNid,b.rsi_hospital as rsi_shopname,b.rsi_doctorcode,b.rsi_doctor,b.rsi_proxycode,b.rsi_proxy,b.rsi_proxy_phone,b.rsi_proxy_email,a.wp_contact_num,a.wp_contact_num_success,a.wp_num2,a.wp_quota,a.completeschedule,a.completeschedulestatus from wp_writepaper_schedule a join rsi_sampleinfo_yezi b on a.wp_AcnID=b.rsi_doctorcode and a.wp_ACNid in (@tsql6) 经过修改 和独立测试 , select distinct b.rsi_areaname,b.rsi_region,a.wp_ACNid,b.rsi_hospital as rsi_shopname,b.rsi_doctorcode,b.rsi_doctor,b.rsi_proxycode,b.rsi_proxy,b.rsi_proxy_phone,b.rsi_proxy_email,a.wp_contact_num,a.wp_contact_num_success,a.wp_num2,a.wp_quota,a.completeschedule,a.completeschedulestatus from wp_writepaper_schedule a join rsi_sampleinfo_yezi b on a.wp_AcnID=b.rsi_doctorcode
[/code]和exec(@tsql6)
都有输出数据,而且关联的wp_AcnID 和 rsi_doctorcode 都是相同的,就是应该是能联合查询到数据的,但为何输出为空
SELECT DISTINCT
b.rsi_areaname ,
b.rsi_region ,
a.wp_ACNid ,
b.rsi_hospital AS rsi_shopname ,
b.rsi_doctorcode ,
b.rsi_doctor ,
b.rsi_proxycode ,
b.rsi_proxy ,
b.rsi_proxy_phone ,
b.rsi_proxy_email ,
a.wp_contact_num ,
a.wp_contact_num_success ,
a.wp_num2 ,
a.wp_quota ,
a.completeschedule ,
a.completeschedulestatus
FROM wp_writepaper_schedule a
JOIN rsi_sampleinfo_yezi b ON a.wp_AcnID = b.rsi_doctorcode
set @sql='select distinct b.rsi_areaname,b.rsi_region,a.wp_ACNid,b.rsi_hospital as rsi_shopname,b.rsi_doctorcode,b.rsi_doctor,b.rsi_proxycode,b.rsi_proxy,b.rsi_proxy_phone,b.rsi_proxy_email,a.wp_contact_num,a.wp_contact_num_success,a.wp_num2,a.wp_quota,a.completeschedule,a.completeschedulestatus from wp_writepaper_schedule a join rsi_sampleinfo_yezi b on a.wp_AcnID=b.rsi_doctorcode and a.wp_ACNid in '+(''+@tsql6+'')
exec (@sql)类似这样,出门上班去,等F版主来看吧