CODEWITHSUNDEEP
sql-server-2008-r2sql-server-2012
KrasshX
KrasshX

Reputation: 142

Convert differences between SQL Server 2008 R2 and 2012

I've got an issue while executing a SQL statement in SQL Server 2012 while it's perfectly working in a SQL Server 2008 R2...

The error message is :

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

I want to execute this SQL Select statement : 

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) 
    )

Could you help me finding out why it's not working with SQL Server 2012 please ?

Upvotes: 4

Views: 1311

Answers (2)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

What is the definition of the table? (IMP_VALUE at least). Why not store numeric in the 1st place... 2008 and 2012 query plan probably differ and does not handle the parameter and condition in the same order.

With SQL Server 2012 (compatibility level 110) you can try to replace the 2 cast:

 AND ISNUMERIC(IMP_VALUE) = 1  
        AND  CAST( IMP_VALUE  as NUMERIC(38,19)) <= 0.469)

by 

AND TRY_CONVERT(NUMERIC(38,19), IMP_VALUE) <= 0.469
AND  TRY_CONVERT(NUMERIC(38,19), IMP_VALUE) BETWEEN 3.684 AND 4.225

It returns NULL without error when it cannot convert it.

Upvotes: 1

ErikE
ErikE

Reputation: 50251

The problem is that conditions are not always evaluated in the order you place them. This means that Cast(IMP_VALUE as numeric(38, 19)) can fail when it is a non-numeric value, if the query execution plan happens to evaluate this before the IsNumeric(IMP_VALUE) = 1.

This is not determined by how many clauses there are or what version of SQL Server you're running--those are only coincidences. The problem is exactly as I described. The fact that you get different results with different queries is due to different queries, or different servers, using different execution plans.

The cure is to make sure that all conditions will not throw an error, no matter what order they are executed in.

For all versions of SQL Server, you can do this:

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

Or, in SQL Server 2012 and up, you can use Try_Convert:

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

If your server complains that this is not a built-in function name, then it is likely that your database was upgraded from a prior version and you need to alter the database compatibility level. Note that doing this could have some other effects in the system that you need to consider, so study up on it first, and try it out in a non-production system before doing it in production (or do it during a non-critical period and set the compatibility level back if you run into any issues). The danger is not that any damage will occur, but that query results could be different or errors could occur (different ordering of query or stored procedure results being a more common occurrence).

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

Upvotes: 2

Related Questions