首页新闻找找看学习计划

这个存储过程数据类型怎么转化 求大神

0
[已解决问题] 解决于 2015-08-10 14:57


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 时出错。

 

 

龙o魂的主页 龙o魂 | 初学一级 | 园豆:198
提问于:2015-08-05 09:26
< >
分享
最佳答案
0

第一个参数传错了,'1.00000000',去掉双引号,或者1,不然SQL会把它当成一个1.00000000的字符串

奖励园豆:5
嗷大喵学编程 | 初学一级 |园豆:165 | 2015-08-05 09:38
其他回答(1)
0

CAST 和 CONVERT函数可以将某种数据类型的表达式显式转换为另一种数据类型,语法:CAST(.....AS char(50)),CONVERT(char(50), .....)

稳稳的河 | 园豆:4183 (老鸟四级) | 2015-08-05 09:38

cast  convert  在  存储过程里的AS    BEGIN  之间加吗

支持(0) 反对(0) 龙o魂 | 园豆:198 (初学一级) | 2015-08-05 10:01

@龙o魂: 我刚看了下你的SQL,发现这2个函数你都在上面用了很多了,你去看看自己的SQL是怎么用的,你的问题大约还是参数错了,第一个与最后一个是int的

支持(0) 反对(0) 稳稳的河 | 园豆:4183 (老鸟四级) | 2015-08-05 15:30
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册