T-SQL equivalent of Excel "MAX" function to return larger of two numbers

Question

In Excel, there's a function called "MAX" that accepts numbers and returns the largest one in the set. Is there a function in T-SQL that duplicates this functionality? I haven't been able to find one, and I've written a UDF that does it for me, but I thought it was worth asking.

Here is the function I've been using:

CREATE FUNCTION dbo.LargerOf
(
    -- Add the parameters for the function here
    @First FLOAT,
    @Second FLOAT
)
RETURNS FLOAT
AS
BEGIN

    DECLARE @Result FLOAT

    IF @First > @Second
     SET @result = @First
    ELSE
     SET @Result = @Second

    RETURN @Result

END
GO

I don't expect any luck, but instead of moving my function to a whole bunch of new servers, I thought I'd at least ask. Thanks!

Answer 1

I don't know if the function you need exists, but for a workaround, I like this one better

set @max = case when @first > @second then @first else @second end

Answer 2

You could use:

CASE
   WHEN @First >= @Second THEN @FIRST
   ELSE @Second
END

Answer 3

Unfortunately not.

A word of warning, for extremely intensive usage, I've found that scalar functions (even those which could be easily inlined with a CASE, like yours) really do not perform well on SQL Server 2005, so if you are dealing with millions of calls, put it inline (sometimes you can fake an inline TVF).

Hopefully, SQL Server will eventually have an inline SVF or have a function equivalent to GREATEST!

Answer 4

declare @first int, @second int

select @first=45, @second=123

select max(a) from (select @first a UNION ALL select @second) x