This question already has answers here:

Why is casting failing when I am using ISNUMERIC()? (2 answers)
Efficient ISNUMERIC() replacements on SQL Server? (11 answers)

Closed 3 days ago.

I have a table with the primary key param "code" typed as string. The key could be numeric or alphanumeric. The idea is to write a query that gives the max value among the numeric values of the keys inside a range.

Consider a table called FooTable with these values as key for these records: "abc", "def", "ghi", "10", "12", "30".

A first try without the range clause works and it's the following:

SELECT MAX(Ex.code) AS maxValue FROM
    (SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1) AS Ex

Result: 1 record with value 30 under column maxValue.

Then I inserted the WHERE clause with the range and SQL Server printed this error:

SELECT MAX(Ex.code) FROM
    (SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1) AS Ex
WHERE Ex.code < 13

Error: Error: Msg 245 – Conversion failed when converting the varchar value ‘abc’ to data type int.

How is it possible that the external query uses the FooTable and not the FROM nested query result set?

Note: This alternative solution works but it uses more than one operation.

DECLARE @Ex TABLE (code int);
INSERT INTO @Ex SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1;
SELECT max(code) AS maxValue FROM @Ex WHERE code < 13;

Result: 1 record with value 12 under column maxValue.

Thanks in advance for the answers.