Looking for a General "Minimum" User Defined Function

Question

I created the following function to simplify a piece of particularly complex code.

CREATE FUNCTION [dbo].[DSGetMinimumInt] (@First INT, @Second INT)
RETURNS INT
AS
BEGIN
  IF @First < @Second
    RETURN @First
RETURN  @Second

END

However, it only works for the INT datatype. I know I could create one for numeric and possibly for Varchar and Datetime.

Is it possible to create one master "Minimum" function to deal with them all? Has anyone done this?

I've Googled it, but come up empty.

Answer 1

All major databases except SQL Server support LEAST and GREATEST which do what you want.

In SQL Server, you can emulate it this way:

WITH    q (col1, col2) AS
        (
        SELECT  'test1', 'test2'
        UNION ALL
        SELECT  'test3', 'test4'
        )
SELECT  (
        SELECT  MIN(col)
        FROM    (
                SELECT  col1 AS col
                UNION ALL
                SELECT  col2
                ) qa
        )
FROM    q

, though it will be a little bit less efficient than a UDF.

Answer 2

here is a basic one you can work with, I'd be careful using this in queries, as it will slow them down in proportion to the number of rows it is used on:

CREATE FUNCTION [dbo].[DSGetMinimum] (@First sql_variant, @Second sql_variant)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @Value   varchar(8000)

    IF SQL_VARIANT_PROPERTY(@First,'BaseType')=SQL_VARIANT_PROPERTY(@Second,'BaseType')
        OR @First IS NULL OR @Second IS NULL
    BEGIN
        IF SQL_VARIANT_PROPERTY(@First,'BaseType')='datetime'
        BEGIN
            IF CONVERT(datetime,@First)<CONVERT(datetime,@Second)
            BEGIN
                SET @Value=CONVERT(char(23),@First,121)
            END
            ELSE
            BEGIN
                SET @Value=CONVERT(char(23),@Second,121)
            END
        END --IF datetime
        ELSE
        BEGIN
            IF @First < @Second
                SET @Value=CONVERT(varchar(8000),@First)
            ELSE
                SET @Value=CONVERT(varchar(8000),@Second)
        END

    END --IF types the same

    RETURN  @Value

END
GO

EDIT
Test Code:

DECLARE @D1 datetime    , @D2 datetime
DECLARE @I1 int         , @I2 int
DECLARE @V1  varchar(5) , @V2 varchar(5)
SELECT @D1='1/1/2010', @D2='1/2/2010'
      ,@I1=5        , @I2=999
      ,@V1='abc'    , @V2='xyz'
PRINT dbo.DSGetMinimumInt(@D1,@D2)
PRINT dbo.DSGetMinimumInt(@I1,@I2)
PRINT dbo.DSGetMinimumInt(@V1,@V2)

Test Output:

2010-01-01 00:00:00.000
5
abc

If you are going to use this in a query, I would just use an inline CASE statement, which would be MUCH faster then the UDF:

CASE
    WHEN @valueAnyType1<@ValueAnyType2 THEN @valueAnyType1
    ELSE @ValueAnyType2
END

you can add protections for NULL if necessary:

CASE
    WHEN @valueAnyType1<=ISNULL(@ValueAnyType2,@valueAnyType1) THEN @valueAnyType1
    ELSE @ValueAnyType2
END