Select
Distinct value as value ,CASE WHEN ISNUMERIC(value) <> 0 THEN CONVERT(int,value) ELSE value END AS converted_value From TableA
This fails for two reasons.
First, '1.0' for example passes the ISNUMERIC test, but can't be cast as an int/
Second, you have part of your case trying to return an INT while the rest returns a string. And that won't work predictably. So this is where the cast as sql_variant is helping.
As to why its a bad idea, what is going to be calling your function/query, and what is it expecting to get? You could have unexpected results.