sql-server-2008-r2 - 转换 SQL Server 2008 R2 和 2012 之间的差异

Question

我在 SQL Server 2012 中执行 SQL 语句时遇到问题，而它在 SQL Server 2008 R2 中完美运行...

错误信息是:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

我想执行这个 SQL Select 语句:

Select 
    count(*)
from IMPORTBM 
    inner join ATTRIBUTE on ATT_ATTRIBUTE_ID = IMP_ATTRIBUTE_ID 
where IMP_LOCATION_ID = 2 
    AND IMP_SERIAL_ID = 310001 
    AND IMP_VERSION_ID = 1 
    AND (
        (ATT_ATTRIBUTE = 'PS_APISizing' 
            AND IMP_VALUE = 'C')
        OR
        (ATT_ATTRIBUTE = 'DTD' 
            AND ISNUMERIC(IMP_VALUE) = 1  
            AND  CAST( IMP_VALUE  as NUMERIC(38,19)) <= 0.469) 
        OR
        (ATT_ATTRIBUTE = 'IOD' 
        AND ISNUMERIC(IMP_VALUE) = 1  
        AND  CAST( IMP_VALUE  as NUMERIC(38,19)) BETWEEN 3.684 AND 4.225) 
    )  

你能帮我找出为什么它不能与 SQL Server 2012 一起使用吗？

Answer 1

问题是条件并不总是按照您放置它们的顺序进行评估。这意味着 Cast(IMP_VALUE as numeric(38, 19))如果查询执行计划碰巧在 IsNumeric(IMP_VALUE) = 1 之前评估它，则当它是非数字值时可能会失败。 .

这不是由有多少子句或您运行的 SQL Server 版本决定的——这些只是巧合。问题和我描述的一样。使用不同的查询获得不同结果的事实是由于不同的查询或不同的服务器使用不同的执行计划。

解决方法是确保所有条件都不会抛出错误，无论它们以何种顺序执行。

对于所有版本的 SQL Server，您可以这样做:

Convert(
   numeric(38,19),
   CASE WHEN IsNumeric(IMP_VALUE) = 1 THEN IMP_VALUE ELSE NULL END
) <= 0.469

或者，在 SQL Server 2012 及更高版本中，您可以使用 Try_Convert :

Try_Convert(numeric(38,19), IMP_VALUE) <= 0.469

如果您的服务器提示这不是内置函数名称，那么您的数据库可能是从以前的版本升级的，您需要 alter the database compatibility level .请注意，这样做可能会对您的系统产生一些其他影响，您需要考虑，因此请先研究一下，然后在非生产系统中进行尝试，然后再在生产中进行(或在非关键时期进行)如果遇到任何问题，请重新设置兼容性级别)。危险不是会发生任何损坏，而是查询结果可能不同或可能出现错误(查询或存储过程结果的不同顺序更常见)。

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = 110; -- SQL Server 2012