Elias Elias - 4 months ago 29
SQL Question

SQL Server - Case Statement

I'm almost certain you cannot do this within the context of the case statement, and I haven't been able to find any documentation about it, but is it possible to do the following:

SELECT CASE WHEN testValue > 2
THEN testValue(Without Repeating it) ELSE FailValue)
END
FROM Table


A better more thorough example:

Select CASE WHEN (Foo-stuff+bar) > 2
THEN Conditional statement without >2 Else "Fail"
END
FROM TABLE


I am looking for a way to create a select without repeating the conditional query.

EDIT: Due to a poor example on my part, and the lack of answers I was looking for:

testValue = (Table.A / Table.B) * Table.C Table.D

SELECT CASE WHEN testValue > 2
THEN testValue ELSE FailValue)
END
FROM Table

Answer

Like so

DECLARE @t INT=1

SELECT CASE
            WHEN @t>0 THEN
                CASE
                    WHEN @t=1 THEN 'one'
                    ELSE 'not one'
                END
            ELSE 'less than one'
        END

EDIT: After looking more at the question, I think the best option is to create a function that calculates the value. That way, if you end up having multiple places where the calculation needs done, you only have one point to maintain the logic.