"Go" implicit conversion of SQL Server scalar expressions

There is a priority problem in the data type in SQL Server. The return result type of a scalar expression is also determined by the type of operand, such as 1 + ' 1 ' =2. Instead of ' 11 ', the precedence of some int is higher than that of the varchar type. So the result of the expression is implicitly converted to the int type.

Also for scalar functions, such as one column of a table is an int, the table has two rows with values of 2 and 3 if you use the AVG function for this column, the result is 2, not 2. 5. But if this column is of type decimal, then the result is 2.5. Because the result type depends on the operational data type.

A case name like the one below

Case

When <logical_expression1> then <int_expression>

When <logical_expression2> then <varchar_expression>

When <logical_expression3> then <decimal_expression>

END

The type of the return value is the decimal type, even if the first expression satisfies a condition.

But

SELECT

Case

When 1 > 1 then 10

When 1 = 1 Then ' abc '

When 1 < 1 then 10.1

END

This will cause a syntax error,

You can then use the sql_variant type,

SELECT
  Case
    When 1 > 1 then CAST (sql_variant)
    When 1 = 1 then CAST (' abc ' as sql_variant)
    When 1 < 1 then CAST (10. As sql_variant)
  END;

"Go" implicit conversion of SQL Server scalar expressions