Error while trying to return one column from SQL Server function-CodePudding

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 ?

CodePudding user response：

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;

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

CodePudding user response：

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;