Query Overwrite Prevention in SQL Server

Question

I have a TypeCode varchar (20) column that has values ​​such as '1', '2', '3', 'FOO', 'BAR'. I need to find the maximum integer that is less than the parameter value. Something like that:

select max(TypeCode) TypeCode
    from table1 a
    left join table2 b on b.table1id = a.id
        and b.TypeCode not in ('FOO', 'BAR')
    where b.TypeCode < @MaxType

It works most of the time, but in some queries, SQL Server decides to convert it to something similar (according to the query plan).

select max(TypeCode) TypeCode
    from table1 a
    left join table2 b on b.table1id = a.id
        and b.TypeCode < @MaxType
        and b.TypeCode not in ('FOO', 'BAR')

This request explicitly causes the following error:

Conversion failed when converting the varchar value 'FOO' to data type int.

I tried to create a view of table2 without the values ​​"FOO" and "BAR" and instead join the view, but the query plan is still the same.

Do you know how to prevent the optimizer from modifying a query?

PS: I know that the table design is not the best, but it is an outdated database, and I can not change it.

Answer 1

, .

, :

• @MaxType varchar(9). , . .

• CASE ISNULL(NULLIF(...)) WHERE. , , . .

• , , (.. TypeCodeID) . CASE . /, , . NOT IN ('Foo', 'Bar') (TypeCode < @MaxType), Foo Bar TypeCode NULL TypeCodeID.

, , , , . , . , , / , , N/A Unknown. , , . , , " ", , , .

Answer 2

, , , . , , .

, CASE - - :

SELECT MAX(TypeCode) TypeCode
FROM table1 a
    LEFT JOIN table2 b ON b.table1id = a.id
WHERE CASE WHEN b.TypeCode IN ('FOO', 'BAR') THEN 99999999999 ELSE CAST(b.TypeCode AS int) END < @MaxType

, , , .