CASE THEN clause always evaluated

Question

I'm doing a SELECT which uses CASE to convert nvarchar values into a proper type, something like this:

SELECT CASE 
    WHEN @propType = 'money' THEN convert(money, datavalue)
    [...]
    ELSE datavalue
END
FROM [...]

However, it seems the convert is always executed, even when @propType is not equal to money. Runnable example:

declare @proptype nvarchar(50)= 'nvarchar'
declare @val nvarchar(10) = 'test'
select 
    case @proptype
        when 'money' then convert(money, @val)
        else @val
    end

Why is this, and how can I get around it? The MSDN documentation says this:

The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.

I'm not sure this is relevant, but the language is somewhat heavy for a non-native, so maybe it is?

Answer 1

Have a look at the following Use caution when Using CONVERT() with CASE or IF functions in Transact SQL (T-SQL)

The first thoughts are generally one of the following "Since the first value evaluated is numeric, it is converted to decimal, and all other data is expected to be a decimal as well" OR "If SQL Server is able to convert ANY of the values to the specified type, then all values are expected to be of the converted type". However, that's not correct (although the second is close)!

The real problem is that if you choose to Convert the values anywhere within the Case statement, the datatype you are converting the values to is the expected type of ALL the values regardless of if they are of that type or not. Further, even if NONE of the values can actually be converted (even if the Convert line of code never executes), ALL of the values are still expected to be of the type specified by the Convert function!