I have twice now been wrong-footed by this SQL Server error message:“Microsoft SQL Server Native Client 10.0” Hresult: 0x80040E07 Description: “Conversion failed when converting the varchar value ‘abcd123’ to data type int.”.
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.