“Incorrect syntax near ‘RETURNS’. Expecting AS, FOR, or WITH.” (SQL User Defined Function) [closed]

Please review the SQL code below for a user-defined function I am trying to create. In SSMS, there seems to be an issue with this part of the code:

 RETURNS VARCHAR(50)

I am getting the error message:

Incorrect syntax near ‘RETURNS’. Expecting AS, FOR, or 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

I think you are mixing up Stored procedure and Functions.You can return data from stored procedure using output variable or you can use return code. The return code will only return a integer value that is generally used as a status code. In your code , it looks like you would like to use Function rather then stored proc.

Please read below for better understanding –

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

Advertisement

Answer