After upgrade to SQL 2005 getting CONVERT error.

I have a query that was working prior to an upgrade to SQL 2005 from SQL 2000. Now, I am getting an "Error converting data type varchar to numeric." error message. It appears to be related to the order of operations of the "WHERE" clause.

Here is the partial WHERE clause:

AND ISNUMERIC(value) = 1
AND CONVERT(numeric(20,2), value) < 0
AND type IN (...

The value field in the database table is defined as a varchar and will contain mixed datatypes. That is why the "ISNUMERIC" line is in the where clause.

Interestingly, if I change the qualified values in the "IN" line to a single value, it works fine. As soon as I introduce a second value within the "IN" line, I get the error message.

Please see below thread for more details:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=768927&SiteID=1

This is by design. You will have to either use CASE expression to perform conditional checks or modify your schema to use the correct data types for the data.

Advice requested - How do I convert a Base36 number to SQL int

Hi,
Does anyone know if it is possible to create a Stored Procedure in
SQL7.0/2000 to do the following:
I need to convert an SQL int value to a Base36 value, and the other
direction Base36 to SQL int.
Example:
Take the (Base10) number 22, and convert to "M".
Take the (Base36) number "M" and convert to 22.
The Base36 digits are:
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
Thanks
Russell Mangel
Las Vegas, NVHi Russel
This is a solution for Hex which could be extended
http://www.umachandar.com/technical...ipts/Main89.htm
http://www.umachandar.com/technical...ipts/Main29.htm
For hex there is also an undocumented procedure xp_varbintohexstr
http://www.umachandar.com/technical...ipts/Main11.htm
John
"Russell Mangel" <russell@.tymer.net> wrote in message
news:ezmZ0qSEFHA.1396@.tk2msftngp13.phx.gbl...
> Hi,
> Does anyone know if it is possible to create a Stored Procedure in
> SQL7.0/2000 to do the following:
> I need to convert an SQL int value to a Base36 value, and the other
> direction Base36 to SQL int.
> Example:
> Take the (Base10) number 22, and convert to "M".
> Take the (Base36) number "M" and convert to 22.
> The Base36 digits are:
> "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
> Thanks
> Russell Mangel
> Las Vegas, NV
>