SQL Server - Case Statement

Elias picture Elias · Oct 7, 2013 · Viewed 102.1k times · Source

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

UnhandledExcepSean picture UnhandledExcepSean · Oct 7, 2013

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.