How to call a function in another Function

sql, sql-function, sql-server, sql-server-2012, t-sql

Solution

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

Problem

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'.

Original source