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
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.