Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered
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])