Posted by Hugo Kornelis on 07/27/06 20:05

On Thu, 27 Jul 2006 12:39:34 +0200, Generale Cluster wrote:

>Hello,
>I need to select the minimum between the result of a function and a
>number, but i can't find a smart way. By now I'm doing like the
>following, but I think is very expensive because it evaluates the
>function twice:
>
>select case when (myfunction())<100 then (myfunction()) else 100 end
>
>Any idea is appreciated.

Hi Generale,

And yet another not-necessarily-smartest way is the use of a derived
table. Like this:

SELECT CASE WHEN res < 100 THEN rest ELSE 100 END AS Result
FROM (SELECT dbo.MyFunction() AS res
FROM ...
WHERE ... ) AS der;

For this specific case, I think I like Chris' suggestion best. However,
there are other cases where you have multiple references to the result
of a calculation; in cases where you can't adapt Chris' suggestion, yoou
can always use the derived table technique.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]