如何創建決議日期的函式

嗨，我有以下代碼來創建一個函式。基本上它會決議日期并根據日期告訴我輪數。

CREATE OR ALTER FUNCTION VITAL_TARGET.roundfind(dates date)
 RETURNS varchar
 
AS

BEGIN


case 
    when dates between '2020-06-08' and '2020-11-14' then return 'Round 1'
    when dates between '2020-11-15' and '2021-02-17' then return 'Round 2'
    when dates between '2021-02-18' and '2021-04-28' then return 'Round 3'
    when dates between '2021-04-29' and '2021-07-16' then return 'Round 4'
    when dates between '2021-07-16' and '2021-10-03' then return 'Round 5'
    when dates between '2021-10-04' and '2021-11-30' then return 'Round 6'
    when dates between '2021-12-01' and '2022-02-01' then return 'Round 7'
    when dates between '2021-02-02' and '2022-03-28' then return 'Round 8'
    when dates >= '2022-03-29' then return 'Round 9'
end;

出現以下錯誤：

Msg 102, Level 15, State 1, Procedure roundfind, Line 1 [Batch Start Line 0]
Incorrect syntax near 'dates'.
Msg 178, Level 15, State 1, Procedure roundfind, Line 18 [Batch Start Line 0]
A RETURN statement with a return value cannot be used in this context.

uj5u.com熱心網友回復：

你的功能有很多。首先，您將引數定義為名為 的列dates，而不是引數 ( @dates)。接下來您定義要FUNCTION回傳 a varchar(1)，這意味著它只會回傳NULLor 'R'。

然后你有你CASE的運算式，它有它的標量值的陳述句；return 'Round 1'不是標量值，而是 s 陳述句。顧名思義，CASE運算式就是一個運算式，因此它回傳一個標量值，而不是陳述句或布爾結果。

在您的CASE運算式中，您還嘗試dates再次參考一個不存在的列，因為它沒有任何內容（沒有FROM）。

你也錯過了一個END，無論是你的CASE表達還是你的BEGIN：

CREATE OR ALTER FUNCTION VITAL_TARGET.roundfind (@dates date)
RETURNS varchar(7)
AS
BEGIN
    RETURN CASE WHEN @dates BETWEEN '2020-06-08' AND '2020-11-14' THEN 'Round 1'
                WHEN @dates BETWEEN '2020-11-15' AND '2021-02-17' THEN 'Round 2'
                WHEN @dates BETWEEN '2021-02-18' AND '2021-04-28' THEN 'Round 3'
                WHEN @dates BETWEEN '2021-04-29' AND '2021-07-16' THEN 'Round 4'
                WHEN @dates BETWEEN '2021-07-16' AND '2021-10-03' THEN 'Round 5'
                WHEN @dates BETWEEN '2021-10-04' AND '2021-11-30' THEN 'Round 6'
                WHEN @dates BETWEEN '2021-12-01' AND '2022-02-01' THEN 'Round 7'
                WHEN @dates BETWEEN '2021-02-02' AND '2022-03-28' THEN 'Round 8'
                WHEN @dates >= '2022-03-29' THEN 'Round 9'
           END;
END;

但是，您可以CASE通過以下方式使您的表達更加簡潔：

CREATE OR ALTER FUNCTION VITAL_TARGET.roundfind (@dates date)
RETURNS varchar(7)
AS
BEGIN
    RETURN CASE WHEN @dates >= '2022-03-29' THEN 'Round 9'
                WHEN @dates >= '2021-02-02' THEN 'Round 8'
                WHEN @dates >= '2021-12-01' THEN 'Round 7'
                WHEN @dates >= '2021-10-04' THEN 'Round 6'
                WHEN @dates >= '2021-07-16' THEN 'Round 5'
                WHEN @dates >= '2021-04-29' THEN 'Round 4'
                WHEN @dates >= '2021-02-18' THEN 'Round 3'
                WHEN @dates >= '2020-11-15' THEN 'Round 2'
                WHEN @dates >= '2020-06-08' THEN 'Round 1'     
           END;
END;

標籤：功能 tsql

上一篇：如何解決應用程式的def函式問題

下一篇：字串似乎沒有回傳