79486702

Date: 2025-03-05 13:58:38
Score: 4
Natty:
Report link

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 statement in a new UDF to assure that db.dbo.my_udf is called once and only once per case statement 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. See the comment thread for more details

Reasons:
  • Blacklisted phrase (2): I am looking for
  • Long answer (-0.5):
  • No code block (0.5):
  • User mentioned (1): @MartinSmith
  • User mentioned (0): @PeterB
  • Self-answer (0.5):
  • Low reputation (0.5):
Posted by: Mike