有一大段SQL语句，如何封装成一个存储过程，以方便其他程序调用？如何将其中的'a.b.c00' 部分用变量代替？

Question

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

Answer 1

那你就在数据库Procedures里新建一个存储过程被 重复的数值或字符 可以在开始时定义常量的。