Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered I have a table that has an NVARCHAR column that contains data that cannot be converted to BIGINT.  I'm well aware of that and have filtered it out using ISNUMERIC(BB.NVARCHARCOL) = 1.  Despite this, I still get an error when trying to query the data stating that Error converting data type nvarchar to bigint.

The following works fine (no errors reported by SQL):

SELECT *
FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
WHERE ISNUMERIC(BB.NVARCHARCOL) = 1


The following throws the error:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ 
WHERE ZZ.MYBIGINTCOL = 1234


This variation also throws the error:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN
        (SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1) BB
      ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    ) ZZ 
WHERE ZZ.MYBIGINTCOL = 1234


Keep in mind that this does not error and returns all the records successfully...

SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1


I was able to find a solution, which was to convert my BIGINT to NVARCHAR in the subquery:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = CAST(AA.MYBIGINTCOL AS NVARCHAR)
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ 
WHERE ZZ.MYBIGINTCOL = 1234


If I insert the records into a temporary table:

SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL INTO #TEMP FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1


I can use that temporary table in a subquery successfully:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN #TEMP BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ 
WHERE ZZ.MYBIGINTCOL = 1234


What in the world is going on?  It seems like SQL refuses to use the subquery to get a smaller result set before running the outer query, it wants to use the whole table regardless of what I do.  SQL Server 2012 Developer Edition
Use try_cast() instead.

select *
from (
  select *
  from myNormalTable AA
  inner join #TEMP BB on try_cast(BB.NVARCHARCOL as bigint) = AA.MYBIGINTCOL
  --where try_cast(BB.NVARCHARCOL as bigint) is not null /* not neccessary for inner join */
 ) ZZ
where ZZ.MYBIGINTCOL = 1234


In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.


try_convert(datatype,val)
try_cast(val as datatype)
try_parse(val as datatype [using culture])