Doing some code review, I came across a piece of code which giving
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
The likely cause was someone entering a ‘n/a’ in the field (the curse of XML coming from slack systems).
Even with good data, this code is not ideal for several reasons:
- Multiple calls to the .value function to get the same data
- Using nvarchar when varchar is sufficient (after all, it’s being cast to a number!)
- Searching the entire XML every call
My proposed alternative TSQL is below which addresses these issues.
One of the problem exposed is an interesting one – there is no IsNaN() function in TSQL which would make this kludge unneeded. So my additional suggestion would be to create a CLR function that provides IsNaN() functionality.