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 ?

I dont know sqlserver, but I suspect several problems. Your select is a table, and your function return a scalar,that cant be right — Lennart - Slava Ukraini, Aug 31 '22 at 05:23
You have a `SELECT` with a `GROUP BY` that most likely will return multiple rows - which one of those rows do you want to use as your return value?? Not clear.... — marc_s, Aug 31 '22 at 05:24
You need to either `RETURN` your select, or assign the value from your select into a variable and return that. That said Inline Table Valued Functions perform much better generally than a scalar function. There are loads of examples you can copy out there, the official documentation would be a great place to start. — Dale K, Aug 31 '22 at 05:24
@Agar123, since the group is fixed by one of the argument that is my guess — Lennart - Slava Ukraini, Aug 31 '22 at 05:33
Your function skeleton syntax has nothing to do with SQL Server UDF, that can return a value (scalar function) from a variable beginning by @, or can return a table (table inline aka ITVF , or multi-statement aka MSTVF) that needs a table definition and must return a SELECT... Check the online ref manual to learn Transact SQL. — SQLpro, Aug 31 '22 at 06:58
@marc_s I want to return one column only - ORDER_VALUE_ADJUSTED. The result of the case when. — Leonardo, Aug 31 '22 at 11:23

score 1 · Accepted Answer · answered Aug 31 '22 at 10:37

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;

score 0 · Answer 2 · edited Aug 31 '22 at 05:40

A guess, can't verify the syntax at the moment:

CREATE FUNCTION order_value 
(@date DATE, 
 @client VARCHAR(50), 
 @order_number VARCHAR(50))
RETURNS DECIMAL(13,2) 
RETURN
SELECT 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;

Error while trying to return one column from SQL Server function

2 Answers2