TRY_CONVERT - sigh.

While it's in many ways better than IsNumeric, TRY_CONVERT does have some problems. Take this example from the documentation:

SELECT

CASE WHEN TRY_CONVERT(float,'test') IS NULL

THEN 'Cast failed'

ELSE 'Cast succeeded'

END AS Result

Now try:

SELECT

CASE WHEN TRY_CONVERT(float,NULL) IS NULL

THEN 'Cast failed'

ELSE 'Cast succeeded'

END AS Result

Did the second cast fail? You don't really know, because NULL is returned when the cast fails, but also when the cast succeeds and the result of the cast is NULL.