The MIN function requires 1 argument(s)

Question

Below is the code snippet in which MIN function using. When execute below code it is giving an error.

CREATE FUNCTION [dbo].[FN_TempCalcTransportExemp]  
(
    @EmployeeID varchar(20),
    @PayElement varchar(20),
    @Month varchar(10),
    @FinYear varchar(10)
)
RETURNS decimal
AS
BEGIN
    DECLARE @TarnsportExemption decimal(18,2) = 0
    DECLARE @TDSIsFullExemption bit
    DECLARE @PermanentPhysicalDisability decimal(18,2) = 0
    DECLARE @UsingComapnyCar bit
    DECLARE @Conveyance decimal(18,2) = 0
    DECLARE @TransYes decimal(18,2) = 0
    DECLARE @TransNo decimal(18,2) = 0

    DECLARE @MinConveyance decimal(18,2) = 0
    DECLARE @MinTransport decimal(18,2) = 0

    SELECT 
        @TDSIsFullExemption = TDSDetailsFullExemption,
        @TransYes = TDSDetailsYes,
        @TransNo = TDSDetailsNo 
    FROM 
        tbl_TDSSettingDetails 
    WHERE
        TDSSettingsDetailID = 2 

    SELECT 
        @Conveyance = @Month 
    FROM
        tbl_Income 
    WHERE
        EmployeeID = @EmployeeID 
        AND Element = @PayElement 
        AND FinancialYear = @FinYear

    SELECT 
        @UsingComapnyCar = UsingCompanyCar,
        @PermanentPhysicalDisability = PermanentPhysicalDisability 
    FROM 
        tbl_Employee_TDS 
    WHERE
        EmployeeID = @EmployeeID  
        AND TDSFinancialYear = @FinYear

    IF (@TDSIsFullExemption = 1)
    BEGIN
        SET @TarnsportExemption = @Conveyance   
    END
    ELSE
    BEGIN
        IF (@UsingComapnyCar = 1)
        BEGIN
            IF (@Conveyance = 0)
            BEGIN
                SET @MinConveyance = 0
            END
            ELSE
            BEGIN
                SET @MinConveyance = @Conveyance
            END

            IF (@PermanentPhysicalDisability = 1)
            BEGIN
                SET @MinTransport = @TransYes
            END
            ELSE
            BEGIN
                SET @MinTransport = @TransNo
            END

            SET @TarnsportExemption = MIN(@MinConveyance, @MinTransport)
        END
        ELSE
        BEGIN
            SET @TarnsportExemption = 0
        END
    END

    RETURN @TarnsportExemption
END

Error Message:

Msg 174, Level 15, State 1, Procedure FN_TempCalcTransportExemp, Line 66
The MIN function requires 1 argument(s).

Answer 1

set @TarnsportExemption = MIN(@MinConveyance,@MinTransport) - The MIN function is not what you think it is.

You probably want to do something like this:

set @TarnsportExemption = CASE WHEN @MinConveyance < @MinTransport THEN 
                               @MinConveyance 
                          ELSE 
                               @MinTransport
                          END

Another option is this:

SELECT @TarnsportExemption = MIN(val)
FROM 
(
    SELECT @MinConveyance as val
    UNION ALL
    SELECT @MinTransport as val
)

And one more option is to use the values clause:

SELECT @TarnsportExemption = MIN(val) 
FROM (VALUES ( @MinConveyance), (@MinTransport)) AS value(val)

Answer 2

Change your min statement like below MIN. Please refer MIN (Transact-SQL)

--fROM
set @TarnsportExemption = MIN(@MinConveyance,@MinTransport)

--To
SELECT @TarnsportExemption = MIN(A) FROM (
SELECT @MinConveyance A
UNION ALL
SELECT @MinTransport
)AS AA

Answer 3

In SQL, MIN function will return you minimum value of a column from selected list; so you can use MIN only in inline queries.

e.g. Select min(Salary) from Tbl_Employee

So, in your case either you can use case when then or union all to get minimum value from two variables as:-

SET @TarnsportExemption = CASE WHEN @MinConveyance < @MinTransport THEN @MinConveyance ELSE @MinTransport END

OR

SELECT @TarnsportExemption = MIN(TEMPS.[VALUE]) FROM ( SELECT @MinConveyance AS VALUE

UNION ALL

SELECT @MinTransport AS VALUE ) AS TEMPS