sknox (8/4/2011)
While it's in many ways better than IsNumeric, TRY_CONVERT does have some problems.
Oddly enough, this is exactly the stance I took a week or so ago in a discussion elsewhere. The simple answer is to add a test for the item being converted being NULL.
It's difficult to add to your example, because it uses a constant, but in real-world applications we will need to be careful to distinguish between the NULL meaning convert-failure, and the NULL meaning input-was-NULL.
In the end, I was convinced that this was a non-issue, largely because the alternatives were worse (e.g. a can-I-convert-this function). I still think it will catch people out, but I am in the minority 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi