sqlsql-servercase-when

CASE-WHEN condition without rewriting the whole statement

I'll start of with a pseudo-code snippet:

CASE
    WHEN [very long expression] > 1 THEN 1
    ELSE [very long expression]
END

Is it possible to avoid rewriting the whole expression?

I already tried creating my own function:

CREATE FUNCTION fn_isLargerThanOne(@v FLOAT)
RETURNS FLOAT
AS BEGIN
    IF(@v > 1)
    BEGIN
        SET @v = 1
    END
    RETURN @v
END

But using this function inside my query slows it down from 2s to 11s. Any ideas?

Solution

  • Use a sub-query to compute the value which you want to use multiple times in the case expression e.g.

    SELECT
    CASE
        WHEN ComputedExpression > 1 THEN 1
        ELSE ComputedExpression
    END
FROM (
    SELECT *
        , [very long expression] ComputedExpression
    FROM MyTable
) x;