Create Table #Mutidata ( FIndex int IDENTITY,FEntryID INT, FBomInterid int, FItemID int null, FNeedQty decimal(28,14) default(0) null, FBOMLevel int null, FItemType int null, FParentID int default(0)null, FRate decimal(28,14) default(0) null, FHistory int default(0) null, FHaveMrp smallint default(0) null, FLevelString varchar(200) null, FBom int, FMaterielType int default(371) null,FOperID int default(0))
Create Table #MutiParentItem( FIndex int IDENTITY,FEntryID INT default(0), FBomInterid int, FItemID int null, FNeedQty decimal(28,14) default(0) null, FBOMLevel int null, FItemType int null, FParentID int default(0)null, FRate decimal(28,14) default(0) null, FHistory int default(0) null, FHaveMrp smallint default(0) null, FLevelString varchar(200) null , FBom int, FMaterielType int default(371) null,FOperID int default(0))
Create Table #Errors ( FIndex int IDENTITY, FType smallint default(0), FErrText varchar(355) )
go
Insert into #mutiParentItem (fbominterid,FItemID,FNeedQty,FBOMLevel,FParentID,FItemType,FBom) Select a.finterid, t1.FItemID,a.fqty, 0,0,t1.FErpClsID,t1.FItemID
From icbom a,t_ICItem t1 Where t1.FItemID = a.fitemid and a.fstatus=1 and t1.fnumber='a.b.c00' --如何将其中的'a.b.c00' 部分用变量代替（这个是一个物料代码）
declare @P1 int
set @P1=0
declare @P2 char(400)
set @P2=' '
exec PlanMutiBomExpandEx 50, 1, '01 1 1900 12:00:00:000AM', '01 1 2100 12:00:00:000AM', @P1 output, @P2 output
go
select a.FIndex,a.FitemID,a.FItemType, a.FBOMLevel,a.FLevelString FLevel,b.fnumber FNumber,b.fname FName,isnull(b.FModel,'') FModel,
a.FNeedQty FQty,d.FScrap,isnull(i.fname,'') FUseStatus,isnull(k.fprice,0) fprice,a.FNeedQty*isnull(k.fprice,0) 金额
from #Mutidata a
inner join t_icitem b on a.fitemid=b.fitemid
inner join icbomchild d on a.FBomInterid=d.finterid and a.FOperID=d.FOperID AND a.FEntryID=d.FEntryID and a.FItemID=d.FItemID
left outer join t_submessage i on b.fusestate=i.finterid
left outer join(select distinct fitemid,sum(a.famount)/sum(a.fqty) fprice
from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate between '2007-01-01' and '2016-12-31'
group by fitemid
union all
select distinct fitemid,sum(a.famount)/sum(a.fqty) fprice
from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate=(select max(z.fdate) from icstockbill z,icstockbillentry x where x.fitemid=a.fitemid and z.finterid=x.finterid and z.ftrantype=1 and z.frob=1 )
and FItemID not in(select distinct fitemid from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate between '2007-01-01' and '2016-12-31')
group by fitemid
)k on k.fitemid=a.fitemid
where 1=1
union
select a.FIndex,a.FitemID,a.FItemType, a.FBOMLevel,a.FLevelString FLevel,b.fnumber FNumber,b.fname FName,isnull(b.FModel,'') FModel,
a.FNeedQty FQty,d.FScrap,isnull(i.fname,'') FUseStatus,isnull(k.fprice,0),a.FNeedQty*isnull(k.fprice,0) 金额
from #Mutidata a
inner join t_icitem b on a.fitemid=b.fitemid
inner join iccustbomchild d on a.FBomInterid=d.finterid and a.FItemID=d.FItemID and a.FOperID=d.FOperID AND a.FEntryID=d.FEntryID
left outer join t_submessage i on b.fusestate=i.finterid
left outer join(select distinct fitemid,sum(a.famount)/sum(a.fqty) fprice
from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate between '2007-01-01' and '2016-12-31'
group by fitemid
union all
select distinct fitemid,sum(a.famount)/sum(a.fqty) fprice
from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate=(select max(z.fdate) from icstockbill z,icstockbillentry x where x.fitemid=a.fitemid and z.finterid=x.finterid and z.ftrantype=1 and z.frob=1 )
and FItemID not in(select distinct fitemid from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate between '2007-01-01' and '2016-12-31')
group by fitemid
)k on k.fitemid=a.fitemid
where 1=1
union
select a.FIndex,a.FitemID,a.FItemType, a.FBOMLevel,a.FLevelString FLevel,b.fnumber FNumber,b.fname FName,isnull(b.FModel,'') FModel,
a.FNeedQty FQty,0 FScrap,isnull(i.fname,'') FUseStatus,isnull(k.fprice,0),a.FNeedQty*isnull(k.fprice,0) 金额
from #Mutidata a
inner join t_icitem b on a.fitemid=b.fitemid
inner join icbom d on a.FBomInterid=d.finterid and a.FItemID=d.FItemID
left outer join t_submessage i on b.fusestate=i.finterid
left outer join(select distinct fitemid,sum(a.famount)/sum(a.fqty) fprice
from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate between '2007-01-01' and '2016-12-31'
group by fitemid
union all
select distinct fitemid,sum(a.famount)/sum(a.fqty) fprice
from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate=(select max(z.fdate) from icstockbill z,icstockbillentry x where x.fitemid=a.fitemid and z.finterid=x.finterid and z.ftrantype=1 and z.frob=1 )
and FItemID not in(select distinct fitemid from icstockbillentry a
left join icstockbill b on a.finterid=b.finterid and b.ftrantype=1 and b.frob=1
where b.fdate between '2007-01-01' and '2016-12-31')
group by fitemid
)k on k.fitemid=a.fitemid
where 1=1
order by FIndex desc
go
drop table #Mutidata
drop table #MutiParentItem
drop table #Errors
那你就在数据库Procedures里新建一个存储过程被 重复的数值或字符 可以在开始时定义常量的。
新建一个存储过程，把上面的语句复制过去，报错！！！（上面的语句单独运行是正常的）
百度----sql 如何创建一个存储过程