在将varchar值转换为数据类型int时失败

Question

SELECT U1.FAuxPropID,t_99.FName AS FAuxPropIDName,t_99.FNumber AS FAuxPropIDNumber,U1.Fauxqty,
	v1.FBillNo,V1.FBizType,t_103.FName AS FBizTypeName,t_103.FID AS FBizTypeNumber,
	u1.FEntryID,t_3.FName AS FEntrySelfP0130Name,t_3.FNumber AS FEntrySelfP0130Number,
	t_2.FName AS FEntrySelfP0131Name,t_2.FNumber AS FEntrySelfP0131Number,U1.FEntrySelfP0132,
	U1.FEntrySelfP0135,U1.FEntrySelfP0130 AS FEntrySelfP0339,U1.FEntrySelfP0131 AS FEntrySelfP0340,
	U1.Fuse AS FEntrySelfP0342,U1.FFetchTime,t_5.FName AS FHeadSelfP0127Name,t_5.FNumber AS FHeadSelfP0127Number,
	v1.FInterID,U1.FItemID,t_4.FUnitGroupID as FItemUnitGroupID,t_4.FNumber AS FItemID_Number,
	t_4.FName AS FItemIDName,t_4.FNumber AS FItemIDNumber,t_4.FPriceDecimal,U1.FQty,
	t_4.FQtyDecimal,U1.FSecCoefficient,U1.FSecQty,V1.FHeadSelfP0127 AS FStockID,t_4.FTrack,
	U1.FUnitID,t_7.FName AS FUnitIDName,t_7.FNumber AS FUnitIDNumber,t_6.FName AS FuseName,
	t_6.FNumber AS FuseNumber
 FROM POrequest v1 WITH (READPAST)
	INNER JOIN POrequestEntry u1 WITH (READPAST) ON  v1.FInterID=u1.FInterID
	LEFT OUTER JOIN t_SubMessage t_103 ON  v1.FBizType=t_103.FInterID
	LEFT OUTER JOIN t_Stock t_5 ON  v1.FHeadSelfP0127=t_5.FItemID
	LEFT OUTER JOIN t_AuxItem t_99 ON  u1.FAuxPropID=t_99.FItemID
	LEFT OUTER JOIN t_Emp t_3 ON  u1.FEntrySelfP0130=t_3.FItemID
	LEFT OUTER JOIN t_Department t_2 ON  u1.FEntrySelfP0131=t_2.FItemID
	LEFT OUTER JOIN t_Exp t_6 ON  u1.Fuse=t_6.FItemID
	LEFT OUTER JOIN t_ICItem t_4 ON  u1.FItemID=t_4.FItemID
	LEFT OUTER JOIN t_MeasureUnit t_7 ON  u1.FUnitID=t_7.FItemID
WHERE ((v1.FInterID=12 AND u1.FEntryID IN (1,2,3,4,5,6)))
ORDER BY v1.FInterID, u1.FEntryID

我这段代码为什么在sql 2000里面运行是成功的，但是在sql 2008里面却报错了！提示“在将varchar值转换为数据类型int时失败”。我看下主要是u1.Fuse=t_6.FItemID 这个有问题，但是为什么在2000里面却能成功运行呢？？

Answer 1

用convert转换：

例如：convert(int,字段名称)

---

If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

Help by clicking：
Click here to donate your rice to the poor
Click to Donate
Click to feed Dogs & Cats

Found any spamming-senders? Please report at: Spam Report

Answer 2

在不改代码的情况下，是否可以设置某个选项，保证这段代码能在SQL 2008里面运行成功呢？

Answer 3

应该是你2000 里面没有会导致转换失败的数据吧

Answer 4

This is data related issue, you can use isnumber function to convert numbers only.

Answer 5

rmiao大侠说得对，用SELECT ISNUMERIC(3) 函数判断字段值是否是整型，如果是不是整型就用convert函数来转换

Answer 6

不能转化肯定是数据格式的问题，查一下数据吧

---

Please Mark As Answer if it is helpful.