How to call a function in another Function

Question

I need to write a script that creates and calls a function named fnItemTotal that calculates the total amount of an item in the OrderItems table (discount price multiplied by quantity).

To do that, this function should accept one parameter for the item ID, it should use the DiscountPrice function that I created earlier and it should return the value of the total for that item.

This is my Function I created and it gets created. But when i try to call it it gives an error.

CREATE FUNCTION fnItemTotal 
        (@ItemID INT )
    RETURNS MONEY
BEGIN
RETURN 
    (
        SELECT 
            ItemId,
            (SELECT * FROM dbo.fnDiscountPrice(ItemID) WHERE ItemID=@ItemID)*Quantity)--The fnDiscountPrice is the Other function i created.
        FROM 
            OrderItems
        WHERE 
            ItemID=@ItemID
    );
END 
GO

This is what I'm using to call it:

Select ItemID,dbo.fnItemTotal(ItemID) AS 'Total Price'
from OrderItems 
ORDER BY ItemID;

This is the ERROR it gives me when I call it:

Msg 208, Level 16, State 3, Line 2 Invalid object name 'dbo.fnDiscountPrice'.

Are you absolutely sure you created the `fnDiscountPrice` function in the `dbo` schema? Are you absolutely sure the function exists within the database you are running the function from? — TT., Oct 23 '16 at 06:34

Hadi · Accepted Answer · 2016-10-23T14:19:59.993

5

Try using the following code:

CREATE FUNCTION dbo.fnItemTotal 
    (@ItemID INT )
RETURNS MONEY
BEGIN
DECLARE @X as MONEY 

        SELECT @X = dbo.fnDiscountPrice(ItemID) *  OrderItems.Quantity 
        FROM 
            OrderItems
        WHERE 
            OrderItems.ItemID=@ItemID

            RETURN @X

END

edited Oct 23 '16 at 14:19

answered Oct 23 '16 at 07:43

Hadi

36,233
13
65
124

This didn't work eithe it says that the item id is ambiguous @H.Fadlallah – Daniel Stallard Oct 23 '16 at 13:36
the main problem was that the function return money by syntax and in real it return a table – Hadi Oct 23 '16 at 14:23
This Worked Thank You Very Much @H.Fadlallah – Daniel Stallard Oct 23 '16 at 16:05

How to call a function in another Function

1 Answers1