存储过程：用户进程中发生浮点异常错误.有很多电话在追着我，帮帮忙，分不够再给！

[chenooo]

以下是我的一个存储过程里的语句，可总是出以上错误，如不 insert into #temp03
就不会出错，这可这么办，无数电话在追着我，各路高手帮帮忙，这里先谢了！

Select a.Job_No,a.KN_Plan_Finish_Date,a.Usage,a.Raw_No,a.GK_No,a.Kn_Plan_Outer_Qty,
a.Order_Qty,d.Usage_Type,e.color_code,
Case d.Usage_Type when 'C' then Round(KN_Plan_Inner_Qty,0) else
Convert(decimal(10,2),0.00) END as Require_Produce_Qty ,
Convert(decimal(10,2),0.00) as Plan_Produce_Qty,
Case d.Usage_Type when 'C' then Round(isnull(Raw_Reserved_Qty,0),0) else
Convert(decimal(10,2),0.00) END as Fabric_Reserved_Qty,
e.Ry_Qty,e.Yarn_Type,e.Yarn_Count,e.Yarn_Lot,e.Job_Item_Id,
[dbo].[Udf_GetOverseaByYarnLot](e.[Job_Item_ID]) as OverSea,
f.Customer,f.Follower,
Convert(decimal(10,2),0.00) as Fact_Ry_Qty,-- 此字段写入实际配纱量
Convert(decimal(10,2),0.00) as Fact_Dy_Qty,-- 此字段写入色纱留位量
Convert(varchar(4000),space(20)) as Ppo_No,
systemdb.dbo.UDF_GetSizeInfo(c.cc_Width,c.cc_Width_Part,c.cc_Height,c.cc_Height_Part,c.Unit) as size
into #temp03
from pcArrangeMain a
left join ArtDB..rtRawInfo c on a.Raw_No=c.Raw_No
left join SystemDB..pbUsageList d on a.Usage=d.Usage
left join pcArrangeDetail e on a.Job_Id=e.Job_Id
Left join SystemDB..pbCustomerList f on a.Customer=f.Customer_Code
-- left Join pcPpoLot f on a.Job_No=f.Job_No
-- inner join pcPpoItem g on
where a.Job_No='SP026582' and a.Confirmed<>'C'
order by a.KN_Plan_Finish_Date


select * from #temp03
drop table #temp03

[OpenVMS]

是不是数据超限?把你的字段的定义长度加大
检查Udf_GetOverseaByYarnLot函数是不是有什么问题?

[warp1977]

拿详细的出错讯息来

[leimin]

Case d.Usage_Type when 'C' then Round(KN_Plan_Inner_Qty,0) else

Case d.Usage_Type when 'C' then Round(isnull(KN_Plan_Inner_Qty,0),0) else

[Yang_]

把那几个case when产生的字段用convert转换一下:
如:
Convert(decimal(10,2),(Case d.Usage_Type when 'C' then Round(KN_Plan_Inner_Qty,0) else
0 END) as Require_Produce_Qty ,

[Yang_]

updated:
如:
Convert(decimal(10,2),(Case d.Usage_Type when 'C' then Round(KN_Plan_Inner_Qty,0) else
0 END)) as Require_Produce_Qty ,

[Yang_]

updated:
如:
Convert(decimal(10,2),(Case d.Usage_Type when 'C' then Round(KN_Plan_Inner_Qty,0) else
0 END)) as Require_Produce_Qty ,

[newly_ignorant]

UP
Round函数只是四舍五入，并不改变数据长度
比如
SELECT ROUND(123.4545, 2)
结果为123.4500

因此需要转换

[N_chow]

SQL Serer的版本??
詳細的錯誤訊息是什麼??

[N_chow]

SQL Serer的版本??
詳細的錯誤訊息是什麼??

[N_chow]

SQL Serer的版本??
詳細的錯誤訊息是什麼??

[chenooo]

错误信息（SQL Server 2000）：
服务器: 消息 3628，级别 16，状态 1，过程 Udf_GetOverseaByYarnLot，行 11
用户进程中发生浮点异常错误。已取消当前事务。

但　函数　Udf_GetOverseaByYarnLot　单独执行是没有问题。
数据也没有超限.

Yang_(扬帆破浪) : 加入Convert()也还是一样错误，我把那几个字段封掉也还是有同样错误。

我真是头痛死了，各路高手，Help me!(解决了按需给分 !)

UP

[N_chow]

果然是Udf_GetOverseaByYarnLot這個Function出錯了，不是此語句的錯。
請把Udf_GetOverseaByYarnLot 里的第11行帖出來，或者把全部代碼都帖出來，我幫你看看。

[newly_ignorant]

建议先删除自定义函数测试

[chenooo]

N_chow(一劍飄香) ，先谢谢了，就靠你了！

第11行就是
　[dbo].[Udf_GetOverseaByYarnLot](e.[Job_Item_ID]) as OverSea

此函数并没有涉及到浮点运算啊，且没有 insert into #temp这句话时也不出错？

以下是此函数的内容:
　CREATE FUNCTION [dbo].[Udf_GetOverseaByYarnLot]
(
@Job_Item_Id int
)
RETURNS varchar(10)
AS
BEGIN
declare @Oversea varchar(10)
declare @Seastr varchar(3000)
set @Seastr=''
select @Seastr=@Seastr+[dbo].[udf_GetYarnOverSea](Yarn_Type,Yarn_Count,Yarn_Lot,Color_Code)
from YarnStoredb..Yarntotalstore with(nolock)
where iden in
(
select distinct Virtual_Iden from pcRyreserve with(nolock) where Job_Item_Iden=@Job_Item_id and status<>'C'
)

if @Seastr<>''
begin
if charindex('Y',@Seastr)>0 set @Oversea='Y' else set @Oversea='N'
end
else
begin
set @Seastr=''
select @Seastr=@Seastr+[dbo].[udf_GetYarnOverSea](Yarn_Type,Yarn_Count,Yarn_Lot,Color_Code)
from YarnStoredb..Yarntotalstore with(nolock)
where iden in
(
select distinct Virtual_Iden from pcDyreserve with(nolock) where Job_Item_Id=@Job_Item_id and status<>'C'
)

if @Seastr='' set @Oversea='NIL'
else
if charindex('Y',@Seastr)=0 set @Oversea='N' else set @Oversea='Y'
end

return @Oversea
END


中间还调用了以下函数：

CREATE FUNCTION [dbo].[Udf_GetOverSea] (@Job_No varchar(15),@GK_No varchar(30))
RETURNS varchar(5)
AS
BEGIN
declare @Over_Sea varchar(5)
declare @SeaStr varchar(3000)
set @SeaStr=''

declare @ItemStr varchar(8000)
set @ItemStr=''
select @ItemStr=@ItemStr+rtrim(convert(varchar(10),Job_Item_Id))+',' from pcArrangeDetail with(nolock) where JOb_no=@Job_No and gk_no=@gk_No

select @SeaStr=@SeaStr+[dbo].[Udf_GetYarnOverSea](Yarn_Type,Yarn_Count,Yarn_Lot,Color_Code)
from Yarnstoredb..Yarntotalstore
where Iden in
(select distinct virtual_iden from pcRyReserve a with(nolock),systemdb.dbo.udf_convertstrtotable(@ItemStr) b
where Job_Item_Iden = b.no and a.status<>'C')

if charindex('Y',@SeaStr)>0
begin
set @Over_Sea='Y'
end
else
begin
select @SeaStr=@SeaStr+[dbo].[Udf_GetYarnOverSea](Yarn_Type,Yarn_Count,Yarn_Lot,Color_Code)
from Yarnstoredb..Yarntotalstore
where Iden in
(select distinct virtual_iden from pcDyReserve a with(nolock),systemdb.dbo.udf_convertstrtotable(@ItemStr) b
where Job_Item_Id = b.no and a.status<>'C')

if charindex('Y',@SeaStr)=0
set @Over_Sea='N'
else set @Over_Sea='Y'
end
Return @Over_Sea
END

[chenooo]

update :
函数中再调用的函数是：

--找国内国外纱
CREATE FUNCTION [dbo].[udf_GetYarnOverSea]
(@Yarn_Type varchar(20),@Yarn_Count varchar(30),
@Yarn_Lot varchar(40),@Color_Code varchar(40))
RETURNS varchar(5)
AS
BEGIN
declare @OverSea varchar(5)
select @OverSea=a.OverSea
from ( select distinct OverSea
from YarnStoreDB..YarnIn
where Yarn_Type=@Yarn_Type and Yarn_Count=@Yarn_Count
and Yarn_Lot=@Yarn_Lot and Color_Code=@Color_Code) a
-- 如为Null 则当国内纱　='N'
Return IsNull(@OverSea,'N')
END

[老乐]

[dbo].[Udf_GetOverseaByYarnLot](e.[Job_Item_ID]) as OverSea
为
([dbo].[Udf_GetOverseaByYarnLot](cast(e.[Job_Item_ID] as int )) as varchar(10))as OverSea
看看。不行的话：将两的函数的内容删除（先去内层的），只写return 'sfasfd'试试
问题到底出在什么地方！

[N_chow]

沒看出問題。
若是不加 INTO #temp03則不會出現錯誤，那你可先試試:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

1、Exec sp_configure 'User option',128
RECONFIGURE WITH OVERRIDE

若還是不行，則打上最新的補丁再試試。

[zqllyh]

试试把数值型的全部转成float 看看

[chenooo]

所有方法都试了，都不行，最后小生用了一个无比丑陋的办法，用一个Query先执行没有Insert into 的语句，再执行存储过程，它就不出错了，为什么我还是不知道？？？

[ccnh]

这个无比丑陋的方法还是我提供的，快散分！！！！！