Return Zero If Value Less Than Zero

Is it possible to return zero if a value is less than zero without using a case statement? e.g. Max(a, 0) <-- 'Max' doesn't exist. I would like my code to be as succinct as poss

Solution 1:

Just for fun:

DECLARE@aINT=-3SELECTCOALESCE(NULLIF (ABS(@a), -@a), 0)

This post just hints in the direction that a CASE expression is a much better option to get the expected result.

NULLIF is, after all, just a fancy CASE. The example query above expands (in the execution plan) to:

CASEWHEN 
        CASE 
            WHEN abs([@a])=( -[@a]) 
            THEN NULL 
            ELSE abs([@a]) 
        END IS NOT NULL 
    THEN 
        CASE 
            WHEN abs([@a])=( -[@a]) 
            THEN NULL 
            ELSE abs([@a]) 
        END 
    ELSE (0) 
END

A suitable CASE expression:

--AllversionsSELECTCASEWHEN@a > 0 THEN @a ELSE 0 END;

-- SQL Server 2012 or later
SELECT IIF(@a > 0, @a, 0);