UNION causes "Conversion failed when converting the varchar value to int"

Question

I tried to search for previous articles related to this, but I can't find one specific to my situation. And because I'm brand new to StackOverflow, I can't post pictures so I'll try to describe it.

I have two datasets. One is 34 rows, 1 column of all NULLs. The other 13 rows, 1 column of varchars.

When I try to UNION ALL these two together, i get the following error:

Conversion failed when converting the varchar value to data type int.

I don't understand why I'm getting this error. I've UNIONed many NULL columns and varchar columns before, among many other types and I don't get this conversion error.

Can anyone offer suggestions why this error occurs?

Solution

The error occurs because you have corresponding columns in the two of the subqueries where the type of one is an integer and the type of the other is a character. Then, the character value has -- in at least one row -- a value that cannot be automatically converted to an integer.

This is easy to replicate:

select t.*
from (select 'A' as col union all
      select 1
     ) t;

Here is the corresponding SQL Fiddle.

SQL Server uses pretty sophisticated type precedence rules for determining the destination type in a union. In practice, though, it is best to avoid using implicit type conversions. Instead, explicitly cast the columns to the type you intend.

EDIT:

The situation with NULL values is complicated. By itself, the NULL value has no type. So, the following works fine:

select NULL as col
union all
select 'A';

If you type the NULL, then the query will fail:

select cast(NULL as int) as col
union all
select 'A';

Also, if you put SQL Server in a position where it has to assign a type, then SQL Server will make the NULL an integer. Every column in a table or result set needs a type, so this will also fail:

select (select NULL) as col
union all
select 'A';

Perhaps your queries are doing something like this.

Comments

[Pieter Geerkens]

I have also encountered this error when a column contains varchar values that are mostly numeric strings such as '20546' the view is queried with said column being compared to an integer instead of the string representation of the integer.

[Admin]

Gordon, I believe your edit hits the mark. My NULL columns were inside a subquery. Yes, I know subquery are not a good practice but I had a reason for doing them. Since the NULL column was in a subquery and I queried them again, SQL Server would assign them a datatype before executing the union, so it assigned int. Not sure why I didn't run into this problem before, but I will look at it again today. Thank you.