Chrissy321 (6/19/2013)
Sorry for the narrative but I can't seem to generate sample data to reproduce my problem...I have a WHERE clause that worked reliably but is now generating the an error: Error converting data type varchar to numeric.
WHERE CASE Column1 WHEN 'ABC' THEN 1 ELSE CONVERT(decimal(20,6),Column2)END) IS NOT NULL
Column1 is varchar(100). Column2 is varchar(50).
If a comment out the WHERE clause and place the code in question in the select statement the query runs.
ISNUMERIC indicates column2 can be converted (when column1 <> 'ABC')
Somewhat mystified...
You have a value in Column2 that cannot be converted to decimal(20,6). ISNUMERIC() is not a reliable way of determining whether a varchar value can be converted to a numeric datatype. Try this:
SELECT ISNUMERIC('$452.00')
SELECT CONVERT(decimal(6,2), '$452.00')
Do you have any other conditions in your WHERE clause? If so, you may not have encountered this error before if those conditions allowed SQL Server to weed out the rows where Column2 cannot be converted to decimal(20,6) before it evaluated the CASE expression.
Jason Wolfkill