IsNumeric and Cast headache

An interesting issue was found by a colleague of mine: an exception when cast a nvarchar column to integer even if the column value was checked with IsNumeric on SQL 2000 SP4:

select c.IDfrom dbo.COM_COMPANY c where IsNumeric(c.CODE) = 1 and convert(c.CODE as int) = 304 and DELETED = 0

As result the following exception were raised:

Msg 245, Level 16, State 1, Line 1Syntax error converting the nvarchar value '9665a' to a column of data type int.

I played a bit with this query and I found the following: if I delete the check for the DELETED flag it works fine. But when I add one more check then it throws an exception. Here the working version:

select c.IDfrom dbo.COM_COMPANY c where IsNumeric(c.CODE) = 1 and convert(c.CODE as int) = 304

But I really need to have more checks, so what can I do?

The answer is:

select c.IDfrom dbo.COM_COMPANY c where DELETED = 0 and     (case when IsNumeric(c.CODE) = 1 then cast(c.CODE as int) else -1 end) = 304

This one works just fine.

Enjoy the workaround ;-)