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

Question

Select 
    CAST(de.ornum AS numeric) + 1 as ornum2 
from Cpaym as de
left outer join Cpaym as de1 on CAST(de.ornum AS numeric) = de1.ornum
where ISNUMERIC(de.ornum) = 1 

I'm trying to get the missing sequence number, but I am getting an error:

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

Answer 1

You have a CAST() in your join predicate, which is evaluated BEFORE the WHERE clause. If de.ornum is not numeric, then this cast will fail.

Also, IsNumeric() doesn't catch all numeric types, but it's perhaps good enough in most cases. Here's the documentation: http://technet.microsoft.com/en-us/library/ms186272(v=sql.105).aspx and here is one (of many) articles describing the problems with IsNumeric(): http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html.

There are a few things you can do:

• Fix your schema so that a column named ornum actually contains a number in all tables that have it.
• Use a CASE expression in your join predicate (slow, but it'll work): ... ON CASE WHEN ISNUMERIC(de.ornum) = 1 THEN CAST(de.ornum AS numeric) ELSE NULL END = de1.ornum
• Use a derived table to pre-convert before the join (also slow, but perhaps less slow -- check the execution plan)

Code:

FROM (
  SELECT de.ornum
    FROM Cpaym AS de
   WHERE IsNumeric(de.ornum) = 1
) AS de
LEFT OUTER JOIN ...