declare @sql varchar(8000),
@i int ,
@id varchar(50)
set @i=0
set @id='XJD20120904000002'
set @sql= 'select m.MaterielName '
select @i=@i+1,@sql=@sql+ ',sum(case s.SupplierName
when cast( ' +SupplierName+ ' as varchar(100)) then num else 0 end) [action '+cast(@i as varchar(50))+ '] '
from (select distinct SupplierName from dbo.TBA_Supplier) tem
select @sql=@sql+ 'from TPurc_RatioPrice as t,
TPurc_EnquiryList as l,
TBA_Supplier as s,
TBA_Materiel as m
where t.EnquiryListNo=l.EnquiryListNo and
t.SupplierID=s.SupplierID and
l.MaterielCode=m.MaterielCode and
l.EnquiryNo='+@id+'
group by m.MaterielName '
@i int ,
@id varchar(50)
set @i=0
set @id='XJD20120904000002'
set @sql= 'select m.MaterielName '
select @i=@i+1,@sql=@sql+ ',sum(case s.SupplierName
when cast( ' +SupplierName+ ' as varchar(100)) then num else 0 end) [action '+cast(@i as varchar(50))+ '] '
from (select distinct SupplierName from dbo.TBA_Supplier) tem
select @sql=@sql+ 'from TPurc_RatioPrice as t,
TPurc_EnquiryList as l,
TBA_Supplier as s,
TBA_Materiel as m
where t.EnquiryListNo=l.EnquiryListNo and
t.SupplierID=s.SupplierID and
l.MaterielCode=m.MaterielCode and
l.EnquiryNo='+@id+'
group by m.MaterielName '
有一年没有碰数据库了,东西都忘记的差不多了,请大家帮我看看错误的所在,谢谢。
@i int ,
@id varchar(50)
set @i=0
set @id='XJD20120904000002'
set @sql= 'select m.MaterielName '
select @i=@i+1,@sql=@sql+ ',sum(case s.SupplierName
when cast( ' +SupplierName+ ' as varchar(100)) then num else 0 end) [action '+cast(@i as varchar(50))+ '] '
from (select distinct SupplierName from dbo.TBA_Supplier) tem
select @sql=@sql+ 'from TPurc_RatioPrice as t,
TPurc_EnquiryList as l,
TBA_Supplier as s,
TBA_Materiel as m
where t.EnquiryListNo=l.EnquiryListNo and
t.SupplierID=s.SupplierID and
l.MaterielCode=m.MaterielCode and
l.EnquiryNo='+@id+'
group by m.MaterielName ' exec(@sql)
一样在执行的时候报“必须声明标量变量 "@sql"。”