if (object_id('proc_meitu') is not null)
drop proc proc_meitu
go
create proc proc_meitu
@FQty int,
@FBOMNumber varchar(30),
@FItemID varchar(30),
@PO varchar(20),
@PR int
AS
select cast(@FBOMNumber as int),cast(@FItemID as int),cast(@PO as int)
BEGIN

with cte as(
select convert(varchar(50),'') as lev,Finterid,convert(varchar(50),'') as fpbomnumber,
Fbomnumber AS FCbomnumber,fitemid,fitemid as fpitemid,0 as fpinterid,
convert(varchar(500),RIGHT('000000'+CONVERT(varchar(10),Finterid),6)) as code from ICBOM where FBOMNumber='BOM000035'
union all
select CONVERT(varchar(50),c.lev+'a'),a.finterid,convert(varchar(50),c.FCbomnumber) as fpbomnumber,
a.fbomnumber as fcbomnumber,a.fitemid,c.fpitemid,a.fpinterid,
convert(varchar(500),c.code+RIGHT('000000'+convert(varchar(10),a.finterid),6)) as code
from (
select t1.finterid as fpinterid,t2.FInterID,t1.FItemID,t2.FBOMNumber
from ICBOMChild t1 inner join ICBOM t2 on t1.FItemID=t2.FItemID
union all
select t1.finterid as fpinterid,0,t1.fitemid,'' from ICBOMChild t1 where not exists
(select * from ICBOM where FItemID=t1.FItemID)) a
inner join cte c on a.fpinterid=c.FInterID
)
select lev,fitemid into #Tem from cte order by code
select replace(lev,'a','------')+convert(varchar(50),m.FItemID) levfitemid,n.FQty,n.FBOMNumber,n.FItemID,P1.PO,f1-f2 as PR
into #tem_1 from #Tem m left join ICBOM n on m.FItemID=n.FItemID left join
(select sum(t1.FQty-t2.FStockQty)as PO,t2.FItemID from POOrderEntry t1 join POOrderEntry t2 on t1.FInterID = t2.FInterID
group by t2.FItemID)P1 on n.FItemID=P1.FItemID left join

(select FQty as f1, a2.FItemID from PORequest a1 inner join PORequestEntry a2 on a1.FInterID=a2.FInterID
)P2 on m.FItemID=P2.FItemID left join

(select FQty as f2,a4.FItemID from POOrder a3 inner join POOrderEntry a4 on a3.FInterID=a4.FInterID
where FCancellation=0 and FSourceTranType=70)P3 on m.FItemID=P3.FItemID

select FQty,FBOMNumber,FItemID,PO,PR into #MEI from #Tem_1
select * from #mei
drop table #MEI
drop table #Tem
drop table #Tem_1
END
exec proc_meitu '1.00000000','BOM000035','2010','4702.00000000','0.00000000'

运行的结果：

消息 8114，级别 16，状态 5，过程 proc_meitu，第 0 行
从数据类型 varchar 转换为 int 时出错。