Question

case when issue

Ms sqlserver, case when expression, T-SQL, Conditional expressions

I recently came across behavior of a "case when" conditional expression that really confuses me. In this case:
--
CASE when isnumeric('-10.00%') > 0 then convert(numeric, '-10.00%') end
----
the behavior is as I expected - the isnumeric() function returns 0 (cuz it ain't no number) so the "then" is not evaluated and a null is returned. HOWEVER, if you simply add an else to the case when expression then there is a failed varchar to numeric conversion. So...
----
CASE when isnumeric('-10.00%') > 0 then convert(numeric, '-10.00%') else 'Not Numeric' end
----
bombs out with error message "Error converting data type varchar to numeric."
Hmmm, that sure does confuse me! If anyone knows what is wrong with my understanding of the behavior I would be ever-so-grateful for an explanation.
Thank you kindly, this one has me reelin'!
zh

You are getting this because the data within the THEN portion of the CASE statement is a number. You would need to put a numeric value into the ELSE portion. Your other option would be to leave the THEN value as a text string.

A single column can only have a single data type. "convert(numeric, '-10.00%')" is a number, while 'Not Numeric' is a string.