sql - 函数总是返回 null

Question

我创建了一个函数，它需要两个数字和运算类型来计算结果。不幸的是，这个函数的所有结果都是空的。

CREATE FUNCTION DBO.CALC
    (@NUMBER1 FLOAT, @OPERATION VARCHAR, @NUMBER2 FLOAT)
RETURNS FLOAT
BEGIN
    DECLARE @RESULT FLOAT

    SET @RESULT = CASE  
                     WHEN @OPERATION = 'DODAWANIE' THEN @NUMBER1 + @NUMBER2
                     WHEN @OPERATION = 'ODEJMOWANIE' THEN @NUMBER1 - @NUMBER2
                     WHEN @OPERATION = 'MNOZENIE' THEN @NUMBER1 * @NUMBER2
                     WHEN @OPERATION = 'DZIELENIE' THEN @NUMBER1 / @NUMBER2
                     ELSE NULL
                  END

    RETURN @RESULT
END

SELECT 
    DBO.CALC(2, 'DODAWANIE', 1), 
    DBO.CALC(2, 'ODEJMOWANIE', 1),
    DBO.CALC(2, 'MNOZENIE', 1), 
    DBO.CALC(2, 'DZIELENIE', 1)

标签： sqlsql-server

Answer 1

您需要在需要添加到的函数中指定LENGTH使用VARCHARso 的时间：LENGTH@OPERATION VARCHAR(LENGTH)

CREATE FUNCTION DBO.CALC(@NUMBER1 FLOAT, @OPERATION VARCHAR(LENGTH), @NUMBER2 FLOAT)
RETURNS FLOAT
BEGIN
DECLARE @RESULT FLOAT
SET @RESULT =CASE 
            WHEN @OPERATION ='DODAWANIE' THEN @NUMBER1 +@NUMBER2
            WHEN @OPERATION ='ODEJMOWANIE' THEN @NUMBER1-@NUMBER2
            WHEN @OPERATION ='MNOZENIE' THEN @NUMBER1*@NUMBER2
            WHEN @OPERATION ='DZIELENIE' THEN @NUMBER1/@NUMBER2
            ELSE NULL
        END
RETURN @RESULT
END

SELECT DBO.CALC(2,'DODAWANIE',1), DBO.CALC(2,'ODEJMOWANIE',1),
    DBO.CALC(2,'MNOZENIE',1), DBO.CALC(2,'DZIELENIE',1)