SQL join between NULL and VARCHAR errors

Question

I have two views with the same columns. One of the columns in the first view is generated on the fly and set to NULL , and the same column in the other view has values ​​stored as varchar . I have a stored procedure that looks like this:

 ALTER PROCEDURE [dbo].[mi_GetLearners] (@centrename nvarchar(200)) AS SELECT [centrename] ,[Name] ,[Username] --generated on the fly as NULL FROM [DB1].[dbo].[vw_Learners] WHERE [centrename] = @centrename UNION SELECT [centrename] ,[Name] ,[Username] --values stored as varchar FROM [Linked_Server].[DB2].[dbo].[vw_Learners] WHERE [centrename] = @centrename 

DB1 is on SQL Server 2008 R2
DB2 is on SQL Server 2005

When starting the stored procedure, I get the following error:

Msg 245, level 16, state 1, line 1 Conversion error while converting value varchar 'someusername' for int data type.

Why is he trying to convert the value to int datatype since the other column is set to NULL ? If I instead changed the second column from NULL to ' ' , then the stored process works fine ... I am really confused why the union between the varchar column and the NULL column generated in the select statement will throw such an error ... any ideas?

EDIT: I'm looking for an explanation, not a solution ...

EDIT 2: Running the following code:

 CREATE VIEW vw_myview AS SELECT NULL AS MyColumn EXECUTE sp_help vw_myview 

Return:

  Type Column_name int MyColumn 

Answer 1

The problem is that NULL is (within some room for maneuver) a member of each data type.

When any SELECT query is executed, the contents of each column must be of the same type and only one type. When there is a mixture of values ​​in the column (including NULL s), the type can obviously be determined by examining the types of non- NULL values, and appropriate conversions are performed if necessary.

But, when all rows contain NULL for a specific column, and NULL not passed to a specific type, then there is no type information to use. Thus, SQL Server, somewhat arbitrarily, decides that the type of this column is int .

 create view V1 as select 'abc' as Col1,null as Col2 go create view V2 as select 'abc' as Col1,CAST(null as varchar(100)) as Col2 

V1 has columns of type varchar(3) and int .

V2 has columns of type varchar(3) and varchar(100) .

Answer 2

I expect the field type to be determined by the first SELECT in the union. You can try to reorder your two options or go to CAST (NULL AS VARCHAR (...)).

Answer 3

I think you need to use the same data type:

 --- AS SELECT [centrename] ,[Name] ,CAST(NULL AS VARCHAR(MAX)) AS [Username] FROM [DB1].[dbo].[vw_Learners] WHERE [centrename] = @centrename UNION ---