转换nvarchar值'7575932'时转换失败。数据类型为int

Question

我有开始没有这个遗留代码...

UPDATE B2C 
SET B2C.dborderid = A.order_number__c 
FROM b2csf B2C 
JOIN Alemania A ON B2C.actualid = A.salesforce_id 

我得到这个错误：

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '7201799-' to data type int.

我继续将其改为：

UPDATE B2C 
SET B2C.dborderid = (CASE 
         WHEN Isnumeric (a.order_number__c) = 1 
          THEN CAST(a.order_number__c AS INT) 
        END) 
FROM b2csf B2C 
JOIN Alemania A ON B2C.actualid = A.salesforce_id 

而且现在我得到

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '7575932.' to data type int.

个

我的问题现在：

• 我怎样才能避免这个错误？我不介意比如输掉'7575932'。 （理想情况下，我想丢弃'7575932.'值）

• 我在尽我所能去遵循最佳实践......正在将列DBorderid改为nvarchar唯一的“最佳实践”替代方案？

（我使用SQL Server 2008）

Answer 1

代替... WHEN Isnumeric (a.order_number__c) = 1 ...使用... WHEN Isnumeric (a.order_number__c + '.0e0') = 1 ...