既然已经是动态语句了,不如把 set @3=@3+' from PR_Batch where (convert(varchar(20),EndTime,120) between '''+convert(varchar(20),@start ,120)+''' and '''+convert(varchar(20),@ende,120)+'''or ('''+convert(varchar(20),@start,120)+'''=''''and '''+convert(varchar(20),@end,120)+'''='''' ) and (unti like ''%''+'''+@unti+'''+''%'' or '''+@unti+'''=''''))' 更改为set @3=@3+' from PR_Batch where (1=1)'IF (@start IS NOT NULL) AND (@end IS NOT NULL) set @3=@3+' and (convert(varchar(20),EndTime,120) between '''+convert(varchar(20),@start ,120)+''' and '''+convert(varchar(20),@ende,120)+''')'IF NULLIF(@until,'') IS NOT NULL set @3=@3+' and (unti like ''%'+@unti+'%'')'
select convert(varchar(10),EndTime,120)as 日期, sum(case MaterialCode when '1#砂' then ActualQuality else 0 end)[1#砂], sum(case MaterialCode when '1#石' then ActualQuality else 0 end)[1#石] from PR_Batch where (convert(varchar(20),EndTime,120) between '2014-09-01' and '2014-09-02'or ('2014-09-01'=''and '2014-09-02'='' ) and (unti like '%'+'1#'+'%' or '1#'='')) group by convert(varchar(10),EndTime,120) order by 日期把红色这一段的逻辑顺序搞清楚 应该就是这里的逻辑问题 该加()限制顺序的时候还是要加上比较好
create proc A @start date,@end date,@unti nvarchar(20) as declare @3 varchar(8000) set @3='select convert(varchar(10),EndTime,120)as 日期' select @3=@3+',sum(case MaterialCode when '''+MaterialCode+''' then ActualQuality else 0 end)['+MaterialCode+']' from (select distinct top 100 MaterialCode from PR_Batch order by MaterialCode)a set @3=@3+' from PR_Batch where (convert(varchar(20),EndTime,120) between '''+convert(varchar(20),@start ,120)+''' and '''+convert(varchar(20),@ende,120)+'''or ('''+convert(varchar(20),@start,120)+'''=''''and '''+convert(varchar(20),@end,120)+'''='''' )) and (unti like ''%''+'''+@unti+'''+''%'' or '''+@unti+'''='''')' set @3=@3+' group by convert(varchar(10),EndTime,120) order by 日期' print(@3) exec(@3) 我挪了下一个右括号的位置,你看下~~
set @3=@3+' from PR_Batch where (convert(varchar(20),EndTime,120) between '''+convert(varchar(20),@start ,120)+''' and '''+convert(varchar(20),@ende,120)+'''or ('''+convert(varchar(20),@start,120)+'''=''''and '''+convert(varchar(20),@end,120)+'''='''' ) and (unti like ''%''+'''+@unti+'''+''%'' or '''+@unti+'''=''''))'
更改为set @3=@3+' from PR_Batch where (1=1)'IF (@start IS NOT NULL) AND (@end IS NOT NULL)
set @3=@3+' and (convert(varchar(20),EndTime,120) between '''+convert(varchar(20),@start ,120)+''' and '''+convert(varchar(20),@ende,120)+''')'IF NULLIF(@until,'') IS NOT NULL
set @3=@3+' and (unti like ''%'+@unti+'%'')'
convert(varchar(10),EndTime,120)as 日期,
sum(case MaterialCode when '1#砂' then ActualQuality else 0 end)[1#砂],
sum(case MaterialCode when '1#石' then ActualQuality else 0 end)[1#石]
from
PR_Batch where (convert(varchar(20),EndTime,120) between '2014-09-01' and '2014-09-02'or ('2014-09-01'=''and '2014-09-02'='' ) and (unti like '%'+'1#'+'%' or '1#'=''))
group by
convert(varchar(10),EndTime,120) order by 日期把红色这一段的逻辑顺序搞清楚 应该就是这里的逻辑问题 该加()限制顺序的时候还是要加上比较好
unti EndTime MaterialCode ActualQuality
1# 2014-09-01 1#砂 100
1# 2014-09-01 1#石 100
2# 2014-09-01 1#石 50
2# 2014-09-01 1#砂 150
现在我运行后得到
日期 1#砂 1#石
2014-09-01 250 150
不管我@unti变量传入何值都是这种结果
declare @3 varchar(8000)
set @3='select convert(varchar(10),EndTime,120)as 日期'
select @3=@3+',sum(case MaterialCode when '''+MaterialCode+''' then ActualQuality else 0 end)['+MaterialCode+']'
from (select distinct top 100 MaterialCode from PR_Batch order by MaterialCode)a
set @3=@3+' from PR_Batch where (convert(varchar(20),EndTime,120) between '''+convert(varchar(20),@start ,120)+''' and '''+convert(varchar(20),@ende,120)+'''or ('''+convert(varchar(20),@start,120)+'''=''''and '''+convert(varchar(20),@end,120)+'''='''' )) and (unti like ''%''+'''+@unti+'''+''%'' or '''+@unti+'''='''')'
set @3=@3+' group by convert(varchar(10),EndTime,120) order by 日期'
print(@3)
exec(@3)
我挪了下一个右括号的位置,你看下~~