“RETURNS”附近的语法不正确。应为AS、FOR或WITH。”(SQL用户定义函数)[已关闭]

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

 RETURNS VARCHAR(50)

我收到错误消息:

“RETURNS”附近的语法不正确。期待作为、为或与。

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
;