"Incorrect syntax near 'RETURNS'. Expecting AS, FOR, or WITH."(SQL 用户定义函数)

Question

请查看下面的SQL代码，了解我正在尝试创建的用户定义函数。 在 SSMS 中，这部分代码似乎存在问题：

 RETURNS VARCHAR(50)

我收到错误消息：

"返回"附近的语法不正确。期待 AS 、FOR 或 WITH。

.SQL：

CREATE PROCEDURE [dbo].[udfGetKillContractTreatmentMostSevere] 
    (@paramContractCode NUMBER(5,0))
RETURNS VARCHAR(50)
AS
BEGIN
    --Return the value into this variable
    DECLARE @GetMostSevereTreatement VARCHAR(50)
    --Severity Table stores the logic for evaluating the severity of the medice
    DECLARE @lkpTreatmentSeverity AS TABLE (MedicineType VARCHAR(2), Severity INT)
    INSERT INTO @lkpTreatmentSeverity 
    VALUES (NULL, 0), ('NAE', 1), ('NAIHM', 2), ('CONVEN', 3), ('UNKNOWN', 4)
    --Query to return the most severe medicine for the contract
    ;
    WITH cteKillTreatment AS 
    (
        SELECT --*
            ks.PLANT_CODE,
            KS.KILL_DATE,
            KS.KILL_ORDER_NBR,
            c.CONTRACT_CODE,
            t.MEDICINE_TYPE,
            ISNULL(t.MEDICINE_TYPE, 'No Treatments') AS 'MedicineType',
            lkpTS.Severity
        FROM
            KILL_SCHEDULE ks
        INNER JOIN 
            contract c ON  ks.CONTRACT_CODE = c.CONTRACT_CODE
        LEFT OUTER JOIN 
            cont_disease cd ON  c.CONTRACT_CODE = cd.CONTRACT_CODE
        LEFT OUTER JOIN 
            treatment t ON  cd.TREATMENT_CODE = t.TREATMENT_CODE
        LEFT OUTER JOIN 
            @lkpTreatmentSeverity lkpTS ON  t.MEDICINE_TYPE = lkpTS.MedicineType
    )
    --Kill Schedule Treatment (1 record only; most severe medicine).
    SELECT
        @GetMostSevereTreatement = 
            CONCAT(cteKT.Severity, ': ', cteKT.MedicineType)  
        --    cteKT.*
        --    , subKT.CountMeds
        --    , subKT.CountDistinctMeds
    FROM
        cteKillTreatment cteKT
    INNER JOIN
        (SELECT TOP 1 
             cte.PLANT_CODE,
             cte.KILL_DATE,
             cte.KILL_ORDER_NBR, 
             cte.CONTRACT_CODE,
             COUNT(*) CountMeds,
             COUNT(DISTINCT cte.MEDICINE_TYPE) AS CountDistinctMeds,
             MAX(cte.Severity) as TreatementMostSevere
         FROM
             cteKillTreatment cte
         GROUP BY
             cte.PLANT_CODE, cte.KILL_DATE, cte.KILL_ORDER_NBR, cte.CONTRACT_CODE
         HAVING
             MAX(cte.Severity) = cte.Severity
        ) subKT ON cteKT.CONTRACT_CODE = subKT.CONTRACT_CODE
                AND cteKT.KILL_DATE = subKT.KILL_DATE
                AND cteKT.KILL_ORDER_NBR = subKT.KILL_ORDER_NBR
    WHERE 
        cteKT.CONTRACT_CODE = @paramContractCode
    RETURN @GetMostSevereTreatement
END
GO
;

Answer 1

我认为您混淆了存储过程和函数。您可以使用输出变量从存储过程返回数据，也可以使用返回代码。返回代码将仅返回通常用作状态代码的整数值。在你的代码中，看起来你想使用函数而不是存储的过程。

请阅读以下内容以更好地理解 -

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017

Answer 2

在存储过程中，RETURN 语句用于立即退出，并且只能（可选）返回表示状态代码的整数表达式。如果未指定整数，则返回值 0（成功）。要返回 varchar（50） 值，您的选项是

1. 可以使用返回单个值的标量FUNCTION。

2. 存储过程可以使用 OUT 关键字设置在参数列表中声明的输出参数，如下所示：

   CREATE PROCEDURE [dbo].[udfGetKillContractTreatmentMostSevere] (@paramContractCode INT, @GetMostSevereTreatment VARCHAR(50) OUT) 
   

然后，您只需设置 out 参数的值，它将从存储过程返回。请注意，一个存储过程可以有多个 out 参数。