I have twice now been wrong-footed by this SQL Server error message:

The statement causing this distress was:

ISNULL(View1.long_name, View1.short_name) AS output_column

Reasonably, ISNULL will expect both arguments to be the same type will attempt conversion if they aren’t.   But in this example both columns long_name and short_name are the same: varchar – aren’t they?

Well, no they aren’t actually.   View1 was hiding some changes that were made to the source table:  long_name was dropped, and to keep things working View1 merely aliased NULL as long_name.     With no other clue, SQL Server interprets the literal NULL as type int, hence the attempted conversion.

T-SQL Pattern: No un-cast literals in views

It’s necessary for numeric literals, fairly common-sense for string literals, but less natural for literal NULL to be cast to the appropriate data type – though a moment’s thought makes it clear why it must.

       SELECT
           NULL as short_name,  -- wrong : what data type will it be? 
           CAST(NULL AS varchar(50)) as long_name  -- the correct pattern

The first line should stand out as unacceptably vague about the data type.