2 xledu xledu 于 2017.01.05 15:06 提问

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

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

3个回答

wangchunyu11155
wangchunyu11155   2017.01.05 15:19

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

xledu
xledu   2017.01.05 15:24

新建一个存储过程,把上面的语句复制过去,报错!!!(上面的语句单独运行是正常的)

csmzl
csmzl   2017.01.05 15:34

百度----sql 如何创建一个存储过程

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!