I love this error. Primarily because it demonstrates two very important things.
- Errors matter. Make sure when you ask someone for help you give them the exact error and circumstances causing the error.
- Experience matters. If you’ve been working with database development for a while you can probably pinpoint exactly what’s causing this error just from the error.
When I was handed this error from one of my co-workers I started by telling them exactly what was wrong, and then out of curiosity started a quick poll.
I’m honestly a little mad at myself because of the three possible answers none of them were actually the correct one. I mean they are all true to a certain extent, but not the real problem.
What’s wrong?
If you look at the error it’s pretty clear what’s happened.
Conversion failed when converting the varchar value ‘NULL’ to data type int.
First of all you have to realize that the varchar value ‘NULL’ does not mean a NULL value. I’ve seen this throw people on more than one occasion. A string containing the word ‘NULL’ is not in any way the same as the value NULL. It’s a string. Someone messed up and put the word NULL in there. Next there is an implicit conversion going on of a varchar to an int.
Basically what happened is that someone stored integers into a varchar column. How do I know that? Well, it works most of the time, which means that there is an implicit conversion of a varchar to an int that’s working. I.E. integers stored in a varchar column.
So, you could say this is a data problem because someone stored the word ‘NULL’ in a column meant for an INT. You could call it a coding problem because the code allowed that to happen.
Both are true, although most likely neither one is really the point. It’s almost certainly a design problem. You should NEVER store numbers in a varchar column, dates in a varchar column etc. Store your data in the appropriate data type and you avoid problems like this. Now that said, it is possible, and just possible, that this column has to be a varchar because sometimes the values are just numbers, sometimes they have letters in them. In that case the problem is that someone compared this column incorrectly to a column that’s an integer. In that case it’s back to being a code problem.
Oh, and I should point out, that in 30 years I can count the number of times I’ve seen the word ‘NULL’ belong in a varchar column on one hand. So yea, regardless there is a data problem.
But primarily, this is a design problem. Put your data into the correct data types people.