Error while trying to return one column from SQL Server function

Question

I have a function in SQL Server with a select statement and I want to return one row calculated inside the select statement - ORDER_VALUE_ADJUSTED.

CREATE FUNCTION order_value 
    (@date DATE, 
     @client VARCHAR(50), 
     @order_number VARCHAR(50))
RETURNS DECIMAL(13,2) 
AS
BEGIN
    SELECT 
        EKKO.EBELN, 
        SUM(CASE WHEN EKPO.NETWR = CDPOS.VALUE_NEW AND CDHDR.UDATE >= @date THEN CDPOS.VALUE_OLD ELSE EKPO.NETWR END) AS ORDER_VALUE_ADJUSTED
    FROM 
        EKKO
    INNER JOIN 
        EKPO_C AS EKPO ON EKKO.EBELN = EKPO.EBELN 
                       AND EKKO.MANDT = EKPO.MANDT
    LEFT JOIN 
        CDPOS_C AS CDPOS ON (EKPO.MANDT + EKPO.EBELN + EKPO.EBELP) = CDPOS.TABKEY
    LEFT JOIN 
        CDHDR ON CDHDR.CHANGENR = CDPOS.CHANGENR
    WHERE 
        EKKO.MANDT = @client 
        AND EKKO.EBELN = @order_number
    GROUP BY 
        EKKO.EBELN

    RETURN ORDER_VALUE_ADJUSTED
END;

I am getting these errors:

Msg 444, Level 16, State 2, Procedure order_value, Line 6 [Batch Start Line 0]
Select statements included within a function cannot return data to a client.

Msg 207, Level 16, State 1, Procedure order_value, Line 22 [Batch Start Line 0]
Invalid column name 'ORDER_VALUE_ADJUSTED'.

How can I solve this issue ? Do I need to rewrite it into a stored procedure ?

Answer 1

Your primary issues are that you are trying to SELECT straight out of the function, and you are not storing the data into variables to RETURN.

But it sounds like you actually need an inline Table Valued Function, rather than a Scalar Function, these are in any case much faster

CREATE OR ALTER FUNCTION dbo.order_value 
    (@date DATE, 
     @client VARCHAR(50), 
     @order_number VARCHAR(50))
RETURNS TABLE
AS RETURN

    SELECT 
        EKKO.EBELN, 
        SUM(CASE WHEN EKPO.NETWR = CDPOS.VALUE_NEW AND CDHDR.UDATE >= @date THEN CDPOS.VALUE_OLD ELSE EKPO.NETWR END) AS ORDER_VALUE_ADJUSTED
    FROM 
        EKKO
    INNER JOIN 
        EKPO_C AS EKPO ON EKKO.EBELN = EKPO.EBELN 
                       AND EKKO.MANDT = EKPO.MANDT
    LEFT JOIN 
        CDPOS_C AS CDPOS ON (EKPO.MANDT + EKPO.EBELN + EKPO.EBELP) = CDPOS.TABKEY
    LEFT JOIN 
        CDHDR ON CDHDR.CHANGENR = CDPOS.CHANGENR
    WHERE 
        EKKO.MANDT = @client 
        AND EKKO.EBELN = @order_number
    GROUP BY 
        EKKO.EBELN
;

An inline table function must be a single RETURN SELECT statement.

You use it like this

SELECT *
FROM dbo.order_value(GETDATE(), 'SomeClient', 'SomeOrder') ov;

Or

SELECT *
FROM dbo.Orders o
CROSS APPLY dbo.order_value(o.Date, o.Client, o.Number) ov;