SQLkiwi (8/4/2011)
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 🙂
I have to agree. Every time I look at things like this my brain starts repeating the following mantra:
"Code to enforce Business Rules should not be in SQL!"
Validation of user interfact data is best done by application Functions or Subroutines, not DML. 😎