-1

I expect a result query with a scalar 5.

But I got error message 245

Conversion failed when converting the varchar value 'FPO' to data type tinyint

What am I doing wrong?

CREATE TABLE TabA (aNumVal TINYINT NOT NULL)

INSERT INTO TabA (aNumVal) VALUES(5)
INSERT INTO TabA (aNumVal) VALUES(2)
    
CREATE TABLE TabB (aCharVal VARCHAR(3) NOT NULL)

INSERT INTO TabB (aCharVal) VALUES('FPO')
INSERT INTO TabB (aCharVal) VALUES('2')

SELECT aNumVal
FROM TabA
WHERE aNumVal NOT IN (
    SELECT aCharVal
    FROM TabB
    WHERE ISNUMERIC(aCharVal) = 1
)
Salman A
  • 262,204
  • 82
  • 430
  • 521
andarvi
  • 110
  • 1
  • 9

2 Answers2

2

SQL server will perform data type conversion when comparing mixed data types. VARCHAR is converted to INT (ref). There is absolutely no guarantee at which stage it is done. It could be done before evaluating WHERE clause if the RDBMS decides so. In fact, the same query might work under different conditions.

The possible work around is to use TRY_CAST which returns null if conversion failed instead of throwing an error:

WHERE aNumVal NOT IN (
    SELECT TRY_CAST(aCharVal AS INT)
    FROM TabB
    WHERE TRY_CAST(aCharVal AS INT) IS NOT NULL
)

The IS NOT NULL is required for NOT IN checks (ref).

Salman A
  • 262,204
  • 82
  • 430
  • 521
0

Your problem is in here

WHERE aNumVal NOT IN (SELECT aCharVal FROM TabB 
                      WHERE ISNUMERIC(aCharVal) = 1)

The SELECT aCharVal from TabB WHERE... will return 2, so your WHERE becomes

WHERE aNumVal NOT IN 2

or in the case of this query

WHERE aNumVal IN 'FBO'
Tom Boyd
  • 385
  • 1
  • 7
  • But I use ISNUMERIC to avoid non-numeric values ​​in the WHERE clause subquery. Why doesn't it work? ISNUMERIC should have filtered out 'FBO'...? – andarvi May 23 '23 at 15:51
  • The subquery does work. The NOT IN is your problem. The only aCharVal where ISNUMERIC = 1 (true) is 2. You then use NOT IN meaning not 2. If it's not 2, the only value left is FPO. – Tom Boyd May 24 '23 at 01:19