On SQL Server, how to find the MAX or MIN value out of a couple of variables

Question

I just found out that the MAX() function in SQL only works on columns.

Is there a similar function I can use to find the max value out of e.g. these four variables?

SET @return = MAX(@alpha1, @alpha2, @alpha3, @alpha4)

Or do I have to put them in a column first (and thus create a table first...;-( )?

Regards

Lumpi

Answer 1

There is no built-in function in T-SQL for this but you can use following

SELECT @result = MAX(alpha)
FROM   (SELECT @alpha1
        UNION ALL
        SELECT @alpha2
        UNION ALL
        SELECT @alpha3) T(alpha);  

or (SQL Server 2008+)

SELECT @result = MAX(alpha)
FROM  (VALUES(@alpha1),
             (@alpha2),
             (@alpha3)) T(alpha);