[SOLVED] Retrieve the value of an input expression from within a case expression in T-SQL

Retrieve the value of an input expression from within a case expression in T-SQL

Consider this form of a case expression:

CASE [input_expression]
    WHEN [when_expression] THEN [result_expression]
    ...
    ELSE [result_expression]
END

And this example of that form:

CASE db.dbo.my_udf(col1)
    WHEN 1 THEN 'A'
    WHEN 2 THEN 'B'
    ELSE CAST(db.dbo.my_udf(col1) AS VARCHAR(12))
END

Notice that I am calling my_udf twice (per row in the resultset that makes it to the ELSE)....

Is there a way to get the [input_expression] value from within the case expression to prevent the second call?

Something like this:

CASE db.dbo.my_udf(col1)
    WHEN 1 THEN 'A'
    WHEN 2 THEN 'B'
    ELSE CAST(@@AWESOME_KEYWORD_THAT_RETRIEVES_THE_INPUT_EXPRESSION_VALUE AS VARCHAR(12))
END

Solution

To recap the great discussion in the question's comment thread...

No, the thing I am looking for does not exist natively. Furthermore, it cannot exist, because db.dbo.my_udf is actually evaluated up to 3 times in my example (once per when or else). @MartinSmith demonstrated that a random bit generating function placed as an input_expression, that can only generate a 0 or 1 if called once, can actually bypass both 0 and 1 in the WHEN because it is evaluated per WHEN. See here: https://dbfiddle.uk/t3ZT1qKu

Given that what I am asking for does not (and can not) exist, there were a few clever workarounds suggested.

@PeterB suggested wrapping the case expression in a new UDF to assure that db.dbo.my_udf is called once and only once per case expression evaluation.

@TN suggested evaluating db.dbo.my_udf in a subquery using cross apply to, which also assures that it is only evaluated once per row.

Both great suggestions.